# Welcome to PowerTable Build no-code Data Apps with approvals, workflows, audit & governance Welcome to PowerTable!!! PowerTable allows business users to build **no-code, writeback-enabled** **table apps** on Microsoft Fabric. In the following sections, we’ll take a look at the following: 1. [**PowerTable: A Quick Overview**](#id-1.-powertable-a-quick-overview) 2. [**PowerTable’s High-Level Architecture**](#id-2.-powertables-high-level-architecture) 3. [**What makes PowerTable unique? - A Summary**](#id-3.-what-makes-powertable-unique-a-summary) 4. [**Additional Resources**](#id-4.-additional-resources) ## **1. PowerTable: A Quick Overview** PowerTable allows you to do the following: ### 1. Build Data Apps that connect LIVE to your modern OLTP or OLAP data platforms PowerTable helps you to build tabular data apps that connect LIVE to your data platform. The product is engineered to support both write-optimized **OLTP** (Online Transaction Processing) platforms and read-optimized **OLAP** (Online Analytical Processing) platforms. The following platforms are supported: ![Supported data platforms](https://1781349671-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKyXRCFh0c08ryiUFvyMV%2Fuploads%2F9j6JygnKPeFJyML7E67Y%2Fimage.png?alt=media\&token=c888424d-ae21-44b3-b42b-aaad59e2cbbc) One of the benefits of using PowerTable is that it maintains LIVE synchronization between the apps and underlying tables. Note that the source and destination tables for the App are the same in this case. ### **2. Consume semantic models and build writeback-enabled apps** PowerTable also allows you to build Data Apps on top of your existing semantic models. These apps can capture user inputs and write back data to your data platform. Unlike the [previous scenario](#id-1.-build-data-apps-that-connect-live-to-your-modern-oltp-or-olap-data-platform), the source and destination data stores are different when you consume semantic models.

### **3. Leverage built-in no-code workflow, automation and governance capabilities** PowerTable comes with built-in access control, workflow, approval, automation, audit, and governance capabilities. These no-code, out-of-the-box capabilities are a great value add for: 1. Migrating your spreadsheet apps such as lists (e.g., Products) or trackers (e.g., Project status). 2. Managing your reference data supporting your reporting and planning applications. ![](https://1781349671-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKyXRCFh0c08ryiUFvyMV%2Fuploads%2FkSB126b4TRgkOT149wCu%2F2.png?alt=media) Note that the master data maintained in your ERP or CRM systems have rigid rules to capture forward-looking data (e.g., a product that is yet to be launched, a company that will soon become a customer, a position for which a candidate has not been hired yet, etc.). PowerTable empowers business users to manage these scenarios with ease without relying on IT. ### **4. Leverage push-down SQL for enhanced scalability** Unlike other products in the market that typically struggle to handle more than tens or hundreds of thousands of rows, PowerTable is highly scalable and can support millions of rows. This is possible as our architecture segregates the user interface from storage and compute and uses pushdown SQL to perform all processing on the underlying database. ![](https://1781349671-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKyXRCFh0c08ryiUFvyMV%2Fuploads%2FFSuuNfLQUUNOZtAhxdCf%2F3.png?alt=media) ## **2. PowerTable's High-level Architecture** PowerTable leverages a unique, industry-leading architecture that helps enterprises increase their Return on Investment (ROI) on their modern data platform. Enterprises have already invested in modern data platforms such as Microsoft Fabric, Databricks, and Snowflake. However, all applications in the market today (such as the Enterprise Planning software) require you to replicate or transfer your data to their designated data store. Any updates you make to the data through these apps are retained in the same store, resulting in a data silo. This approach increases cost & complexity for enterprises as they need to perform ETL and complex integrations. Most often, they also need to move the data from such dedicated data stores back to their data platform. PowerTable, on the other hand, syncs LIVE with your modern data store, avoiding replication of data. In addition, it utilizes push-down SQL to run all processes on the data platform, enhancing scalability and performance. The modern architecture is summarized below. ![](https://1781349671-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKyXRCFh0c08ryiUFvyMV%2Fuploads%2FUJvXTC0fMQGPiAImfp4d%2F4.png?alt=media) ## **3. What makes PowerTable unique? - A Summary** Compared to other products in the market, PowerTable is unique as it allows you to do the following: * Build modern data apps that sync LIVE to your modern OLTP/OLAP data store * Consume semantic models and build writeback-enabled apps * Leverage no-code workflow, automation and governance capabilities * Utilize push-down SQL for enhanced scalability * Leverage your existing security and authentication provider ...and more. ## **4. Additional Resources** For any questions or feedback, visit our community portal here: [https://community.powertable.com.](https://community.powertable.com/) # Get Started Let us get started with PowerTable. This section covers the prerequisites for creating a PowerTable item, its supported data platforms, and the admin setup guide for adding the necessary workload to your Fabric workspace for a free trial. > [Prerequisites](/explore-powertable/get-started/prerequisites) > [Supported Data Platforms](/explore-powertable/get-started/supported-data-platforms) > [Admin Setup Guide (15-day Free Trial)](/explore-powertable/get-started/admin-setup-guide-15-day-free-trial) # Prerequisites Before you set up PowerTable, you must ensure you meet the prerequisites outlined below. Reach out to your Fabric administrator for further assistance. ## **Prerequisites:** * **Microsoft Office 365 credentials:** PowerTable is a native Microsoft Fabric workload, so you require your Microsoft O365 credentials to log in. * **Tenant Access:** Ensure you’re connected to your organization's tenant. * **Paid or trial Fabric capacity:** Ensure your workspace has a paid or trial Fabric capacity.

* **Workload Settings:** Capacity admins and users with relevant permissions can add Fabric workloads and make them available either to the entire tenant or to a specific capacity. Make sure a Fabric admin grants the necessary permissions to users who wish to add and use workloads. * **Access to Workspace:** Ensure that you have the necessary access permissions (Admin or Member access) to relevant workspaces so you can create PowerTable apps and workspace items.

* Ensure that the following resource providers are registered in your Azure subscription: * **Microsoft.Storage** * **Microsoft.Sql** * **Access to databases/warehouses:** Ensure you have access to the necessary data platform, database, lakehouse, or warehouse. * **Whitelist IP addresses in Azure SQL firewall rules:** Network firewalls block connections from IP addresses that do not have permissions. You need to whitelist PowerTable's outbound IPs in your Azure SQL database firewall rules. If you encounter any challenges in setting up the PowerTable workload, do not hesitate to reach out to us at our [PowerTable Community](https://community.powertable.com/home). #### References 1. Workspace license information: 2. Workspace roles: 3. Who can add Fabric Workloads - # Supported Data Platforms PowerTable currently supports the following OLTP and OLAP data platforms. * **Online Transaction Processing (OLTP) platforms** * Azure SQL * Fabric SQL * PostgreSQL * **Online Analytical Processing (OLAP) platforms** * Databricks * Snowflake * Amazon Redshift * BigQuery

# Admin Setup Guide (15-day Free Trial) Lumel PowerTable is available as part of the **Lumel EPM Workload**, and you need to ensure that it is provisioned in your workspace before starting to work with it. Let us walk through the steps for a quick setup. {% hint style="info" %} Please refer to the [prerequisites ](/explore-powertable/get-started/prerequisites)before provisioning the workload for a seamless experience. {% endhint %} ## 1. Provision the Lumel EPM workload 1. Log on to Microsoft Fabric. Click on **Workloads** in the left navigation panel.

If you do not see the 'Workloads' option, click the '**…'** button to view more options and select it from there.

2. Choose **Lumel EPM** workload.

3. Click **Add Workload.**

4. Choose the capacities you want to add this workload to. Select the capacity assigned to your workspace. Then click **Add Workload.**

5. Click **Continue** to view the permissions. Then, click **Accept** to grant the requested permissions.

Your 15-day free trial is now activated.

You can now create a workspace on Fabric capacity. ## 2. Create Workspace on Fabric capacity (if not created already) Please follow the steps illustrated in the image below to create a workspace on Fabric capacity.

# PowerTable - Product tour This section will take you through a product tour. To begin, you can explore the sections below: > [Explore a sample app](/explore-powertable/powertable-product-tour/explore-a-sample-app) > [Build your first app](/explore-powertable/powertable-product-tour/build-your-first-app) > [Layout & User Interface](/explore-powertable/powertable-product-tour/layout-and-user-interface) # Explore a sample app This section walks you through a sample PowerTable app so you can **explore PowerTable without having to connect to your database.** 1. Log on to Microsoft Fabric. Click on **New item** to create a new PowerTable item in your workspace.

2. Select **Lumel PowerTable** from the list of items.

3. Enter a name for your PowerTable item and select the workspace. Click **Create.** A PowerTable item gets created.

4. Click on **Explore PowerTable.**

{% hint style="info" %} You may also select 'Create a New App' to build your first PowerTable app. Please refer to [this section](/explore-powertable/powertable-product-tour/build-your-first-app) to know more. {% endhint %} 5. You can explore PowerTable with any of our sample templates. Select any one and click **Create.** Let's start with a simple template that includes two tables: **Employee Management.**

6. Two tables—'Department' and 'Employee'—are loaded into PowerTable for you to explore its features.

7. The **Home** tab includes options for importing and inserting rows, grouping records, sorting, searching, and filtering records, as well as layout settings, audit trails, export options, and more.

8. The **Format** tab lets you customize appearance, formatting, and display-related settings.

9. The **Setup** tab quickly allows you to configure the column properties and manage row and column access control, automation workflows, forms, layout and table settings.

Set up columns, access control, automation and forms

10. The column configuration settings, collaborative commenting, and approval workflow configuration are all available here, as highlighted below.

This documentation provides detailed information on all of the above functionalities and how to use them. In the next section, we'll look at the steps to build your first PowerTable app. # Build your first app PowerTable is a native workload on Microsoft Fabric. This section will help you create your first app in a few simple steps. **There are several ways to create an app with PowerTable:**

	Option	Description
1	Import Spreadsheet	Import data from a spreadsheet and start building your app.
2	Connect to Database	Connect to an existing database table and build an app.
3	Type Data Directly	Enter data directly into the app to create a new table in the database.
4	Connect to Semantic Model	Connect to a semantic model to build a writeback-enabled app.

{% hint style="info" %} Note that in the above methods #1 to #3, the source and destination for the app will be the same - i.e., underlying database table. When you connect to a semantic model (method #4), the semantic model would be the source, and the destination will be the database table. You can use this method to build writeback-enabled applications. You also have the option of scheduling a refresh from the semantic model. {% endhint %} For now, we will explore method #1, where we import data from a CSV file and build an app. ## Create a New PowerTable item 1. Go to your Microsoft Fabric workspace folder. Click on **New item** to create a new item.

2. Select **PowerTable** from the list of items. You may want to add it to your favorites by clicking on the star icon.

3. Enter a name for your PowerTable app and select the workspace. Click on **Create.**

## Create a New App 4. Click on **Create a New App** to create your app. You may also select 'Explore PowerTable' to [experience a sample PowerTable app](/explore-powertable/powertable-product-tour/explore-a-sample-app).

## Create a Database Connection When creating a new table or connecting to an existing table in a database, you need to select your preferred data store and establish a database connection. Your app synchronizes its data with this data store. 5. Follow the steps below to create a new connection: * Click on **New Connection.** * Choose your preferred data platform. We'll connect to the Fabric SQL database. * Click **Next.**

{% hint style="info" %} If you already have a shared database connection or a private connection to the required data store, skip step 5. Instead, click on the 'Existing Connection' tab, select the one you want to use, and then click Next. {% endhint %}

6. **Setup Connection:** Enter the connection type and a unique connection name. Select the workspace and the database from the list of options and click **Next.** Your new private connection is now created.

## Create a New Table 7. **Choose your table where the data will be stored:** Here, you have two options: * Select **Existing Table** to connect to an existing table in the database (or) * Select **New Table** to create a new table in the database. For our demonstration, we'll select **New Table** to create a new table and import our CSV data**.** We'll also choose a database schema (in this case— 'dbo').

8. Enter a name for the table and choose **Upload Excel/CSV.** {% hint style="info" %} Note that you can also choose to connect to an existing semantic model or enter data manually within the app. These are covered in the following sections. {% endhint %}

9. Select the file you want to upload.

10. Preview the uploaded data and click **Next.**

## Edit Column Properties (Optional) PowerTable detects column properties automatically. You can edit them if needed. Choose the primary key. You can also add additional columns in this step, using the **Add Column** option. After verifying the details, click **Finish.**

You have now successfully built your first app using PowerTable.

To get the most out of PowerTable, you will need to perform one or more steps listed below. We'll cover them in detail later. * Configuring field properties * Defining access control permissions * Configuring workflow approvals * Setting up automation including triggers, cascading updates, and email/Microsoft Teams notifications * ... and more. # Layout & User Interface Let us now explore the PowerTable interface. It has the following sections:

### Menu bar PowerTable contains the **Home** and the **Setup** menus. On the right, there are additional tabs for managing column width, approvals, comments, and saving changes. ### Toolbar The toolbar displays relevant features and settings based on the selected menu. * The **Home** tab includes the following options: row import, row insertion, appearance and display settings (such as row height, word wrap, and header fit), conditional formatting, sort, search, and filter options, find and replace, audit, and undo/redo operations.

When you select a row or a column, the toolbar displays basic and advanced formatting and editing options, as shown below:

* The **Setup** tab enables you to configure column properties, manage row and column access control, create automation workflows, and view and manage table settings.

### **Table** This section displays your source table, as well as the results of any editing, sorting, filtering, formatting, commenting, or other actions you perform on it. Here is where you will interact with the data and configure other functionalities that you choose.

### **Status bar** The status bar displays source and table information, settings, and pagination controls.

On the left, the total number of rows, columns, the source and table details are displayed. * **Rows:** The total number of rows in the source table is shown here. * **Columns:** The total number of columns in the source table is shown here. * **Source:** The database connection name and the table name are displayed here. Hovering over it displays the complete source details. * **Sync (Data Refresh):** Click on this option to sync your table data with the source data. This is similar to a source data refresh. {% hint style="info" %} You need to use the **Save** option to save the new changes and updates you made to the table. Use the **sync** option to refresh table data with database data, which also refreshes any database columns or reference columns added to the table. {% endhint %} * **Zoom-in and zoom-out controls:** Users can click on the '+' and '-' icons to zoom in and out, respectively. Alternatively, the Ctrl + and Ctrl - shortcut keys can also be used. On the right, you can view page display settings and pagination controls: * **Pagination controls:** Use these controls to go to the first page, previous, next, and last page, and also navigate to a specific page by entering the page number. * **Settings:** Here, you can choose whether you want the table to span multiple pages or have a scrollbar. It also allows you to change the number of rows displayed per page by selecting 'All', 'Auto', or a specific range from 10 to 75 rows. # Create Table App There are several ways to create an app with PowerTable: > [Import your spreadsheet data](/explore-powertable/powertable-product-tour/build-your-first-app) and build an app > [Connect to an existing database table](/explore-powertable/create-table-app/connect-to-database) and build an app > [Directly type in data in the app](/explore-powertable/create-table-app/enter-data-manually) and create a table in the database > [Connect to an existing semantic model](/explore-powertable/create-table-app/connect-to-semantic-model) and build a writeback-enabled app The upcoming sections will demonstrate each of these steps. # Import Spreadsheet Data Storing data in a database ensures better scalability, security, and querying capabilities compared to spreadsheets. PowerTable lets you import your spreadsheet data and transform it into a dynamic table app, seamlessly integrated with your preferred data store. We have already demonstrated importing spreadsheet data to create our first PowerTable app. You can refer to [this section](/explore-powertable/powertable-product-tour/build-your-first-app) to know the steps. # Connect to Database In the previous section, we demonstrated the steps to [build your first app by importing your CSV data](/explore-powertable/powertable-product-tour/build-your-first-app). In this section, we'll connect to your existing database and create an app using PowerTable. **The source and destination will be the same**, so we will connect to and writeback the changes to the same database. Let us look at the steps. 1. Go to your Microsoft Fabric workspace folder. Click on **New item** to create a new item.

2. Select **PowerTable** from the list of items. You may want to add it to your favorites by clicking on the star icon.

3. Enter a name for your PowerTable app and select the workspace. Click on **Create.**

4. Click on **Create a New App** to create your app. You may also select 'Explore PowerTable' to [experience a sample PowerTable app](/explore-powertable/powertable-product-tour/explore-a-sample-app).

5. **Choose your data source:** The next step is to select an existing database to connect to. After connecting to it through PowerTable, your app synchronizes its data with the database. Let's say you want to connect to sales data residing in a Microsoft Fabric SQL database. Follow the steps below to create a new connection to your preferred data store: * Click on **New Connection.** * Choose your preferred data platform. We'll connect to the Fabric SQL database. * Click **Next.**

{% hint style="info" %} If you already have shared database connections or a private connection to the required data store, skip step 5. Instead, click on the 'Existing Connection' tab, select the one you want to use, and then click **Next.** {% endhint %}

7. **Choose your table:** In this step, you have two options : 1. Select **Existing Table** to connect to an existing table in the database (or) 2. Select **New Table** to create a new table in the database. Since we want to connect to an existing database, we'll choose **Existing Table.** We'll enter the schema name and the table name 'Sales' to connect to it. Click **Next.**

8. PowerTable detects column properties automatically. You can edit them if necessary. Select the primary key. After verifying the details, click **Finish.**

You have now successfully connected to a database and created an app using PowerTable.

You can start working with this data, making changes as needed and configuring access control, workflows, approvals, and other features for your new app. These are covered in the upcoming sections. # Enter Data Manually In previous sections, we demonstrated how to [import a CSV file into PowerTable](/explore-powertable/powertable-product-tour/build-your-first-app) and [connect to a database](/explore-powertable/create-table-app/connect-to-database) directly from PowerTable. This section explains how to manually enter data into PowerTable to build an app. **The source and destination will be the same**; we will enter data to create a table and then writeback the subsequent changes to the same database. Let us look at the steps. 1. Go to your Microsoft Fabric workspace folder. Click on **New item** to create a new item.

2. Select **PowerTable** from the list of items. You may want to add it to your favorites by clicking on the star icon.

3. Enter a name for your PowerTable app and select the workspace. Click on **Create.**

5. **Creating a new database connection:** The next step is to create a new database connection to your preferred data source. Your app synchronizes its data with this data store. Follow the steps below to create a new connection: * Click on **New Connection.** * Choose your preferred data platform. We'll connect to the Fabric SQL database. * Click **Next.**

7. **Choose your table where the data will be stored:** Here, you have two options: 1. Select **Existing Table** to connect to an existing table in the database (or) 2. Select **New Table** to create a new table in the database. We'll select **New Table** to create a new table and type in the data.

8. Select the schema and enter a name for your table. Choose the option **Enter Data Manually** to manually enter the data. Click **Next.**

9. **Add Column Details:** Start creating your table by adding the column name, primary key, and other relevant details like length, precision, scale, etc., wherever applicable.

10. **Add Column:** Click on '+ Add Column' at the top to add a new field/column.

11. You can use the bin icon to delete a column.

12. Click **Finish** after entering all the column details.

The table has been successfully created using PowerTable, with the configured columns.

You can now insert rows into this table using the **Insert Row** option.

With this table app, you can start working on your data, adjusting it as necessary and setting up features like approvals, workflows, and access control. These are covered in the following sections. # Connect to Semantic Model This section explains the steps to connecting to data in a semantic model. PowerTable allows you to connect to an existing semantic model and create an app. The data table, along with any changes or updates, is written back to your preferred data platforms. In this case, **the source and destination are different.** The steps are as follows: 1. Go to your Microsoft Fabric workspace folder. Click on **New item** to create a new item.

2. Select **PowerTable** from the list of items. You may want to add it to your favorites by clicking on the star icon.

3. Enter a name for your PowerTable app and select the workspace. Click on **Create.**

5. **Creating a new database connection:** The next step is to create a new database connection to your preferred data source. Your app **writes back** its data to this data store destination. Follow the steps below to create a new connection: * Click on **New Connection.** * Choose your preferred data platform. We'll connect to the Fabric SQL database for writeback. * Click **Next.**

7. **Select your table where the data will be stored/written back:** Here, you have two options: 1. Select **Existing Table** to connect to an existing table in the database (or) 2. Select **New Table** to create a new table in the database. We'll select **New Table** to create a new table where we will writeback the source data acquired from the semantic model.

8. Enter the following details: * **Schema and Table Name:** Select the schema and enter a name for the table. * **Import Data:** Choose the option **Connect To Semantic Model.** * **Workspace and Semantic Model:** Enter the name of the workspace in which your semantic model exists, as well as the name of the semantic model you want to connect to and get data from. Click **Next.**

{% hint style="info" %} Capacity workspaces with Pro licences are not supported. Please make sure to select a workspace of premium capacity. {% endhint %} 9. **Map Data:** The next step is to map and assign your data. Assign the required fields and values to the table.

10. **Filter:** You can use the filter to select specific fields in the table, if necessary.

11. **Schedule Refresh:** You can also schedule periodic data source refreshes to ensure your data is up to date with the semantic model. To schedule a refresh, enable the toggle. Then, set the refresh frequency, scheduled duration and time, time zone, and option to skip specified calendar holidays. Click **Next** after previewing the data.

12. PowerTable automatically detects the fields' data and input types and the primary key. You can edit them, if necessary. You can also add additional columns in this step, using the **Add Column** option. After verifying the details, click **Finish.**

The table app has been successfully created using PowerTable, with the configured columns and values.

You can start working with this data and make changes as needed. The changes are written back to the configured data platform destination. You can configure access control, workflows, approvals, automation and other features for your new app. These are covered in the upcoming sections. # Working with PowerTable This section describes some of the actions you can perform with your table app, such as configuring fields, editing data, adding or deleting rows, importing records from files, bulk editing, sorting, filtering, conditional formatting, and so on. In the subsequent sections, you can explore other advanced topics such as setting up access control for rows and columns, approval flows, forms, etc. Let's start with column setup in the next section. # Column Setup This section guides you on how to add new columns to your table and configure column properties such as the input type, constraints, default values, lookup values, display name, and other column attributes, such as column width and access control. Explore them below: * **Adding New Columns:** * [Visual Column](/explore-powertable/working-with-powertable/column-setup/add-visual-column) * [Formula Column](/explore-powertable/working-with-powertable/column-setup/add-formula-column) * [Database Column](/explore-powertable/working-with-powertable/column-setup/add-database-column) * [Configure Column Properties](/explore-powertable/working-with-powertable/column-setup/configure-column-properties) * [Manage Column Width, Access & Other Settings](/explore-powertable/working-with-powertable/column-setup/manage-columns) * [Column Width Adjustment Settings](/explore-powertable/working-with-powertable/column-setup/change-column-widths) #### PowerTable allows you to add and work with columns with the following data types: #### Database Columns

Field type	Description
Number	The number field type is intended to hold numerical values such as the number of stocks available, employee salaries, and so on.
Text	The text field type can hold variable-length string of characters, including letters, numbers, and special symbols. Examples include product or employee names, their details or descriptions, etc.
Options - Single select	A single select field is ideal when you want to be able to select a single option from a preset list of options.
Checkbox	A checkbox field is useful to indicate true/false values or a toggle within a record.
Image	An image field allows you to store a single image URL for each record and display a miniature of the image in each cell.
Person	A Person field type allows you to store a single user's email address in each cell, either as the person's name or email ID. When you click on this type of cell, you will see a drop-down list of all users in your organization's Microsoft Entra ID.
URL	A URL field is intended to store a single URL in each cell. Clicking on a URL takes you to that URL.
Email	An email field is intended to store a single email address in each cell.
Phone number	A phone number field allows you to save phone numbers for each record. A leading +, spaces, parentheses, and numbers 0–9 up to 15 digits are supported.
Currency	A currency field is a specific number-type field that formats the number as a currency amount. E.g., when you select 'Currency' as the input type for the number field and 'US Dollar' as the currency type, 3000.5 will be formatted as $3,000.5. All global currencies are supported.
Rating	The rating field is a number-type field that can be used for whole numbers (INT). It allows you to assign ratings to your records for ranking or quality assessment purposes. Three different style formats are available for indicating ratings.
Multiple select	A multiple select field is similar to a single select field; it allows you to select predetermined options from a list. Unlike a single select field, however, you can select as many options as you'd like per cell.
Button	The button field adds interactivity to your record, such as opening a URL or executing an automation. Create clickable buttons to open a URL or execute a defined action.
Date & Time	A date field allows you to easily enter a date and optionally a time into a cell. When editing a date time, you will be presented with a calendar widget that makes it easy to select a specific date.
Formula	Formulas allow you to compute a value in each record based on other cells in that same record.
Lookup	A lookup field allows you to look up a specific field in a linked record.
Percent	The percent field type is a field type designed to hold numerical values formatted as percents. The values in a percent field are all represented as fractions of 100 with the percent sign; e.g. the number .75 in a percent field is represented as 75%.

* Decimal * Date Time * Date * Image * Person The number column type can also be customized to have ratings in the form of icons and stars, as seen on common e-commerce sites. Further, you can configure additional properties based on the selected input type—such as constraints, scaling, etc., which are covered in the upcoming sections. # Adding Columns - Overview With growing business requirements, the need to add new columns to your table can arise more often than not. PowerTable allows you to add new columns to an existing table on the fly to accommodate your changing needs. A new column can be added to a table in two scenarios: 1. **While creating a new table:** The **Add Column** option comes in handy during the initial table configuration step, using which you can add and configure a new column in addition to the imported columns.

2. **While working with an existing table that you have already created or connected to:** You can add a column at any time after creating or connecting to a table using one of the options highlighted below.

In the next sections, we will go over how to add a visual column, a formula column, and a database column to your existing table. * [**Visual column:**](/explore-powertable/working-with-powertable/column-setup/add-visual-column) These are columns that are added on the visual level and are available only in the PowerTable item. These include [multi-select relationship columns](/explore-powertable/working-with-powertable/column-setup/add-visual-column/multi-select-relationship-column), [reference columns](/explore-powertable/working-with-powertable/column-setup/add-visual-column/reference-column), [relation columns](/explore-powertable/working-with-powertable/column-setup/add-visual-column/relation-column), and button-type columns. * [**Formula column:**](/explore-powertable/working-with-powertable/column-setup/add-formula-column) These are calculated columns that you can include in the table. PowerTable supports a wide range of Excel-like formulas and functions, which you can use to create the calculated columns. * [**Database column:**](/explore-powertable/working-with-powertable/column-setup/add-database-column) These are columns that are directly added to the source database via PowerTable. # Add Visual Column The **Add Visual Column** option lets you insert new columns to the table visually. While these columns are configured and saved in the PowerTable item, they **would not be added to the database**. Visual columns include: * [Multi-select Relationship column](/explore-powertable/working-with-powertable/column-setup/add-visual-column/multi-select-relationship-column) * [Reference column](/explore-powertable/working-with-powertable/column-setup/add-visual-column/reference-column) * [Relation column](/explore-powertable/working-with-powertable/column-setup/add-visual-column/relation-column) * [Button column](/explore-powertable/working-with-powertable/column-setup/add-visual-column/button-column)

These are explained in detail in the next sections. # Multi-select Relationship Column A multi-select column allows users to select and enter multiple values for a single record in the field. PowerTable allows you to add multi-select columns sourced from related tables. You can configure the multiple entries for a field to be retrieved from another table in the database. To insert a multi-select visual column, follow the steps below. Let us consider adding the 'Sales Rep' column to the 'Products' table, which allows users to enter sales representatives for each product. 1. Go to **Setup > Columns.**

2. Select **Add Visual Column > Add Multi-select Relationship Column.**

Alternatively, you can insert a new column from the 'Home' tab, as shown below:

3. Enter the column name. Let's add a 'Sales Rep' column that will allow users to assign multiple sales representatives to each product in the 'Products' table.

4. The sales representative details are available in another table with the 'Sales Rep Key' field and other fields.

{% hint style="info" %} Note that a separate relationship table must exist in the database with these three fields—Primary ID, Product Key, Sales Rep Key. If you do not already have a relationship table, create one manually using PowerTable \ (using the ‘New Table’ option) {% endhint %} 5. Enter the lookup table details from which you want to look up and retrieve values. We would like to get the first names of sales representatives from the 'Sales Rep Table' and display the list in the dropdown.

6. Let us now set up the relation table. Enter the name of the relation table, the corresponding reference column to map the lookup key values in the relation table, and the identifier column details that match the primary keys of the current and relation tables.

This stores or updates the mapping between products and sales reps in the relationship table as we assign and unassign sales reps to the products in the app. Click **Save.** We have now successfully added a multi-select relationship column, which allows you to select and assign multiple sales representatives to each product.

After assigning, you can click **Save** and **Proceed** to save your changes to the database.

The relation table is also simultaneously updated based on the selected values. # Reference Column Adding a reference column involves connecting one table to another based on matching columns, enabling you to reference the required columnar data from the connected tables for each record. When you have multiple tables with a few shared columns, you can easily add reference columns to refer to data from the connected tables. #### To add a reference column: 1. Go to **Setup > Columns.**

2. Click **Add Visual Column > Add Reference Column.**

Alternatively, you can insert a new column from the 'Home' tab, as shown below:

3. Enter the name of your visual column. We want to include the order date, so we enter the name 'Order Date'. We can also change the type to 'Date'.

4. Let us now enter the column and table details that we want to refer to. Enter the connection details, schema name, reference table name, column to be referenced, and the column that corresponds to both current and reference tables. Click **Save.** In the example below, we have referred to the 'Orderdate' column from the 'Sales' table, whose connection details we have entered.

The visual column is added to the table, and it looks as shown below:

Reference columns are non-editable, and their values only change when the underlying column values change. # Relation Column Use the relation column to connect a master table (also called a parent table) to its detail/child tables. A relation column links multiple records from one child table to a single record in the master table. This is called a [**master-detail relationship**](/explore-powertable/master-detail-tables-parent-child-linked-records). [This section](/explore-powertable/master-detail-tables-parent-child-linked-records/creating-master-detail-tables) explains in detail the steps to include a relation column that establishes such a relationship between multiple tables. # Button Column A button column is an interactive column that can be added to your table and configured to open a URL or execute an [automation](/explore-powertable/automation). 1. To add a new column, go to **Setup > Columns.**

2. Click **Add Visual Column > Add Button Column.**

Alternatively, you can insert a new column from the 'Home' tab, as shown below:

3. Enter the details about the column: * **Column Name:** Enter a name for the column. * **Label:** The default label is 'Click Here'. If necessary, you can edit the label for the displayed link. * **Action:** Choose the action you want PowerTable to perform when you click the link. You can choose to open a URL or execute an automation, as required.

4. **URL:** If you choose to open a URL, enter the URL details in the section below. Choose **Static** to enter a static URL, or **Formula** to enter a formula to generate a dynamic URL based on data. #### Static URL - example

#### Formula - example

5. Click **Save.** The button column is added as shown below:

# Attachments Column You can attach multiple files directly to records, streamlining your reviews, discussions, and collaboration. By combining this feature with PowerTable's commenting and approval workflow capabilities, your team can now centralize feedback, track updates, discuss, collaborate, and manage asset approvals—all without leaving PowerTable. Say goodbye to scattered tools and enjoy a more efficient, integrated workflow for your digital assets.

What's in? * Add file attachments directly to table records from your local system. * The maximum size supported for each file is 25 MB. There is no theoretical limit to the number of files you can attach to a record. * Access to these files is governed by [row access permissions.](https://docs.powertable.com/explore-powertable/working-with-powertable/column-setup/add-visual-column/pages/zDmYsHLaOQ7uXJjBcZUz#id-2.-update) * PowerTable users with relevant row access can download and save these files to their system. The supported file types for attachments are listed [here](/explore-powertable/working-with-powertable/column-setup/add-visual-column/attachments-column/supported-file-types-for-attachments). # Supported file types for attachments

File Extension	Description	MIME type
.3gp	3GPP multimedia file	video/3gpp
.3g2	3GPP2 multimedia file	video/3gpp2
.7z	7-Zip compressed file	application/x-7z-compressed
.aac	AAC audio	audio/aac
.avi	AVI video file	video/x-msvideo
.bmp	BMP image	image/bmp
.csv	Comma-separated values	text/csv
.doc	Microsoft Word document (97-2003)	application/msword
.docm	Microsoft Word macro-enabled document	application/vnd.ms-word.document.macroEnabled.12
.docx	Microsoft Word document (2007 or later)	application/vnd.openxmlformats-officedocument.wordprocessingml.document
.epub	EPUB e-book file	application/epub+zip
.gif	GIF image	image/gif
.gz	GZIP compressed file	application/gzip
.ico	Icon file	image/vnd.microsoft.icon
.ics	iCalendar file	text/calendar
.jpg, .jpeg	JPEG image	image/jpeg
.json	JSON data	application/json
.mid, .midi	MIDI audio file	audio/midi
.mp3	MP3 audio file	audio/mpeg
.mp4	MP4 video file	video/mp4
.mpeg	MPEG video file	video/mpeg
.oga	Ogg audio file	audio/ogg
.ogv	Ogg video file	video/ogg
.ogx	Ogg file	application/ogg
.one	Microsoft OneNote file	application/onenote
.opus	Opus audio file	audio/opus
.otf	OpenType font	font/otf
.pdf	PDF document	application/pdf
.png	PNG image	image/png
.ppsm	PowerPoint macro-enabled slideshow	application/vnd.ms-powerpoint.slideshow.macroEnabled.12
.ppsx	PowerPoint slideshow	application/vnd.openxmlformats-officedocument.presentationml.slideshow
.ppt	PowerPoint presentation (97-2003)	application/vnd.ms-powerpoint
.pptm	PowerPoint macro-enabled presentation	application/vnd.ms-powerpoint.presentation.macroEnabled.12
.pptx	PowerPoint presentation (2007 or later)	application/vnd.openxmlformats-officedocument.presentationml.presentation
.pub	Microsoft Publisher document	application/vnd.ms-publisher
.rar	RAR compressed file	application/x-rar-compressed
.rpmsg	Outlook email message	application/vnd.ms-outlook
.rtf	Rich Text Format document	application/rtf
.svg	Scalable Vector Graphics image	image/svg+xml
.tar	Tar archive file	application/x-tar
.tif, .tiff	Tagged Image File Format	image/tiff
.ttf	TrueType font	font/ttf
.txt	Text document	text/plain
.vsd	Microsoft Visio drawing	application/vnd.visio
.wav	Waveform Audio File Format	audio/wav
.weba	WebM audio file	audio/webm
.webm	WebM video file	video/webm
.webp	WebP image file	image/webp
.wma	Windows Media Audio file	audio/x-ms-wma
.wmv	Windows Media Video file	video/x-ms-wmv
.woff	Web Open Font Format	font/woff
.woff2	Web Open Font Format 2.0	font/woff2
.xls	Microsoft Excel spreadsheet (97-2003)	application/vnd.ms-excel
.xlsm	Microsoft Excel macro-enabled spreadsheet	application/vnd.ms-excel.sheet.macroEnabled.12
.xlsx	Microsoft Excel spreadsheet (Open XML)	application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xml	Extensible Markup Language file	application/xml
.log	Log file	text/plain
.zip	zip file	application/zip

# Add Formula Column PowerTable has a built-in formula engine that can perform calculations on your data. Formula columns are calculated columns created from your data that enable you to derive business-specific metrics or calculations on the go. You can add them at the visual level or directly to your source database. 1. To insert formula columns, go to **Setup > Columns.**

2. Select **Add Visual Column > Add Formula Column.**

You can also insert a formula column as shown below:

3. Configure the formula column by entering the column name and required formula, then click **Save**.

* You can create a formula column directly in the database by selecting the checkbox labeled "**Create a database column**." * Select the appropriate data type for the column. The formula column will be added to the source database.

Creating a formula column in the database

The formula column has now been added successfully.

Formula columns cannot be edited directly because their values are based on an underlying formula. For more information about formulas and functions, you can refer to [this section.](/explore-powertable/formulas-and-functions) # Add Database Column PowerTable lets users add new columns to the source database directly through the app. This feature eliminates the need for users to engage with the database directly, enhancing security and efficiency. {% hint style="info" %} Please ensure you have the required permissions to the database you are connected to, as you are adding a column directly to the source database. {% endhint %} Let us consider the example below where we will add the approver details column to the 'Product SubCategories' table. 1. To add a new column to the source, click on **Add Database Column.**

2. Enter the column name and its data type. You can optionally make it a non-nullable column and assign a default value. Then click **Save.** Here, we have added the 'Approvers' column with the data type VARCHAR.

3. Configure the column properties for the newly added column, as you would for any other columns, and then click **Save**.

4. Now you can view the newly added column in the app, where you can start updating the details. The newly added column as well as the changes are synced with the source database.

{% hint style="warning" %} Please exercise caution when adding columns. Once created, they cannot be modified or deleted from the app directly—you must access the database to do so. {% endhint %} # Configure Column Properties Configuring column properties ensures that your data is accurate and consistent in the database. Setting the right data types, constraints, and relationships prevents errors and improves data integrity. It keeps the database manageable and scalable, making future updates easier. ## 1. Column set up in PowerTable After loading your data into PowerTable, you can configure the column properties in the **Columns** section. The column properties include the input type, whether the column is editable or not, its default value, minimum and maximum values (constraints), lookup and relation, prefix, suffix, etc. * While importing CSV data or creating a database from scratch, you will be allowed to configure these properties during the initial steps. * When connecting to a database, PowerTable automatically detects and configures the input type and other basic settings. You can either retain them or change them as needed to meet specific business requirements. You can modify the column properties at any time by selecting **Setup > Columns**.

You can also click on **Manage Columns > Edit Config** to configure the column properties.

This opens the column setup window, as shown below.

## 2. Display The 'Display' field allows you to display or hide a column from the table. You can clear this box to hide a specific column. This option can be useful when certain data does not need to be displayed but should remain in the table.

{% hint style="info" %} To adjust the width of columns, click and drag the column headers manually or configure them from the [**Manage Columns**](/explore-powertable/working-with-powertable/column-setup/change-column-widths) menu. {% endhint %} ## 3. Required This field specifies whether the user must enter the values in the column or if they can be left blank. Checking this box makes the column mandatory. You can specify which columns are required. Typically, primary keys and business keys are considered required columns.

## 4. Configuring Column Properties To edit a column's properties, click the pencil icon next to it or double-click the column name.

The column properties are broadly classified into [**General**](/explore-powertable/working-with-powertable/column-setup/configure-column-properties/general) and [**Display**](/explore-powertable/working-with-powertable/column-setup/configure-column-properties/display) properties. * Configuring general properties includes defining the **column's input type, constraints, and default value.** * Display properties include a **column's display name and its description**. For number-type columns, you can also configure the required **prefix, suffix, and thousand separator**. We will explain each of the above in the upcoming sections. # General In this category, you can define the following properties: 1. [Type](#id-1.-type) 2. [Constraints](#id-2.-constraints) and 3. [Default Value](#id-3.-default-value) ## **1. Type** Specify the input data type to be allowed in each column using this dropdown.

PowerTable allows the following data types as inputs: * Number * Decimal * Text * Date Time * Date * Single Select * Check Box * Image * Person * URL * Email Further, you can configure additional properties based on the selected input type. Example: For number-type input, you can set the minimum and maximum allowed values. For single-select type, you can either manually configure the dropdown values or fetch them from a lookup table. These properties are explained in the next sections. ## **2. Constraints** This section provides options for limiting input values and configuring field validation. ### **2.1. Minimum and Maximum** Here you can specify the minimum and maximum values that can be entered into the columns. Any values outside of this range are not permitted. These limits can be applied to the input types: *Number, Decimal, Date Time,* and *Date*. Consider the example below, where the minimum and maximum values are set for the ***Product Cost*** column.

The existing values remain unchanged. However, if a user enters a value that falls outside the specified range, they will be notified of the minimum and maximum limits.

user notified about the min and max values

**Note:** You can also set only one of these limits (minimum or maximum) and not both. For the 'Date' and 'Date Time' input types, users may choose a date range or both date and time range. Any date or time outside of this range will not be accepted.

### **2.2. Field Validation** This option is available for the text input type and allows you to specify which types of inputs can be entered into a column. This way, you can ensure that the data is correct and accurate. You can allow users to enter any value or only numeric, alphanumeric, non-numeric, email, URL, or regex-matching inputs.

With Regex, you can specify a pattern and allow only matching inputs. In the example below, we have entered the regex pattern against which the user input will be validated.

The entered value is validated against the specified input type configuration. Inputs that do not match the pattern are identified and rejected.

## 3. Default Value PowerTable lets you configure default values for the columns. When a new row is added, the specified column is automatically filled with the default value if you have set one. ### 3.1. Manual value To set a static value, select **Manual** and enter the value. In the example below, we have added 'NA' as the default value for the 'Product Description' column.

Whenever a new row is added, this column value is automatically populated with 'NA' by default, which can be modified later.

#### Reset to default on update Checking the 'Reset to default on update' option not only sets the default value for the column but also disables the column from editing. The existing values are retained, but they are automatically changed to the default value only when users edit other values in the specific record. Find below an example where the existing value changes to the default value after an update.

* For the 'Decimal' input type, you can specify the default number of decimal digits to be allowed.

* For the 'Person' input type, you can either use their name or email address as default. * For the 'Single-select' type, you can choose the default value from the list of options you have configured.

Configuring default value for single-select type

* For the 'Check Box' type, the default values are automatically configured with the Boolean values: true and false. You can change these to 1 and 0 or yes and no, etc., if necessary.

Configuring default value for checkbox type

### 3.2. Derived value You can set derived values as defaults instead of using static values. These derived values are calculated using configurable formulas. PowerTable provides many Excel-like formulas and functions to help you perform these calculations. These values change dynamically as the underlying reference data changes. Derived values can be set for the field types - **Number, Decimal, Text, Date, Date Time, Person, and Email.** #### Recalculate data on update Checking the 'Recalculate data on update' option sets the calculated value as default for newly inserted rows and also disables the column from editing. The existing values are retained, and they are automatically changed to the derived value only when users edit other values in the specific record. We have discussed setting the type, constraints, and defaults for table data. In the next section, we will explore how to select and look up values from a table or visual for a single-select field type. # Lookup and Relation PowerTable allows you to configure any field in the table using the single-select input type. The lookup and relation field is specifically designed for setting up single-select type columns.\ \ In addition to manually configuring the dropdown options, PowerTable offers additional methods for sourcing values from dimensions, even if they reside in a different table or data source, through the use of lookup tables. In summary, we have the following methods to configure the options: 1. [Manual:](#id-1.-manual) Configure the dropdown options manually. 2. [Distinct Values:](#id-2.-distinct-values) Use the existing values in the column as a drop-down list with distinct options. 3. [Lookup Table:](#id-3.-lookup-table) Source values from another table in a database, which is usually done for foreign key fields. This section will provide a detailed procedure for configuring dropdown values for each method. ## 1. Manual This method allows you to type in the drop-down options and labels manually. Let us take an example where the ***Product Color*** column options are set manually:

1. Click on the pencil icon beside the ProductColor field. 2. Choose **Single Select** and then **Manual** in the Type fields. 3. Enter the options and labels as required. 4. Click **Save** to save the configuration. 5. Click **Add** to add new options and the bin icon to delete an option.

You can now edit the table with the added dropdown options. PowerTable differentiates the single-select options by using different text-fill colors.

## 2. Distinct Values When you select this option, PowerTable creates a distinct list of options from the existing values. 1. In the table below, the ***Product Name*** field already contains a list of product names.

2. When you choose Distinct, these values become the drop-down options. Click **Save.**

You can now choose the product name from the list of distinct values that has been created. Notice how the column values are color-coded to indicate a single-select dropdown list.

## 3. Lookup Table PowerTable allows you to look up and source values from other tables as well. 1. When you select 'Lookup,' the highlighted section appears, where you can enter the schema name, the lookup table, the current table column containing the keys, and the lookup table column from which the drop-down options should be sourced.

**Note:** If there are any common columns between the lookup table and the current table, you can also set up filter criteria based on those matching columns. Only drop-down values that meet the filter criteria will be displayed, ensuring accuracy and ease of option selection.

Filter based on value in matching column

2. Click **Save** to save the configuration. The existing sub-category keys are replaced with their corresponding sub-category names using lookup.

For newly inserted rows, you can use the drop-down to select from these lookup values. In the next section, we will look at the display settings. # Display The Display section allows you to specify how the column header and their values should be displayed based on your needs. Go to **Setup > Columns.**

Click on the pencil icon beside the column name or double-click on the column name. Go to the **Display** tab.

Here, you can add and configure various display properties, such as the date format, display name, thousand separator, prefix, and suffix. ## 1. Format Using the Format property, you can define how the date and date-time values should be presented in the columns. You can choose from various date and date-time formats such as 'MM/DD/YYYY', 'DD/MM/YYYY', 'DD MMM, YYYY hh:mm:ss', and so on.

## **2. Display Name** The Display Name property enables you to display a user-friendly name for the column header, different from the actual field name in the database. For instance, the 'ProductSubcategoryKey' field in the database can be displayed as 'Product SubCategory' in the visual for enhanced clarity.

## **3. Description** You can use the 'Description' field to include additional information in the column headers, helping users understand the data better. An 'i' icon is added beside the header name, and when you hover over it, the additional description is shown.

## **4. Thousand Separator** By default, numbers in the column are displayed continuously without any thousand separators. To enhance the readability of large numeric values, you can add a comma, a period, or a space as a separator for every group of thousands.

## **5. Prefix** This property allows you to add a prefix of up to two characters before numeric or decimal column values. This is commonly used to indicate currency symbols.

## **6. Suffix** This property lets you add a suffix of up to two characters after numeric or decimal column values. This can be used to indicate measurement units like kilograms, centimeters, and percentages. We have now discussed all the column properties that can be configured from the **Columns** section. In the next section, we will look at other options, such as importing/exporting configurations and adding columns. # Manage Columns We provide a dedicated menu to manage all column properties, such as column width, visibility, column input type configurations, and access control. To change any of the column attributes listed above, click on **Manage Columns**.

## 1. Adjust Column Width You can specify the width of each column in pixels. Enter the column width in pixels for the columns that you want to adjust.

You can use the **Reset** option to restore the column widths to their original values before any changes were made.

## 2. Display/Hide Columns To display or hide specific columns, check or uncheck the checkbox beside them. In the example below, we have hidden the 'Product Description' column.

Enable the checkbox in the header to display all columns in the table.

## 3. Column Configuration Click on **Edit Config** to open the column configuration window where you can configure all of the column properties.

This opens the window as shown below. Refer to [this section](/explore-powertable/working-with-powertable/column-setup/configure-column-properties) to know more.

## 4. Column Access Control Clicking on the **Column Access** option allows you to specify who can access which columns.

The following window opens where you can configure the column access control. To know more, refer to [this section](/explore-powertable/access-control/column-permissions).

## 5. Re-order Columns To rearrange columns, simply drag and drop the desired columns onto the table, or you can arrange the column headers here, as shown below.

#### Search and Filter You can use the search bar to find and customize specific columns here, especially if there are many columns in the table. You can also filter specific columns by data type or column type.

# Change Column Widths There are several ways to quickly change the width of columns, particularly when your table has a large number of columns. The simplest way is to hover over the column header that needs to be resized and drag the handle to the desired size.

There are other preset and manual column width adjustment options available from the toolbar. 1. ‘Fit to header’ resizes the columns to fit the column header. 2. ‘Fit to content’ resizes the columns to fit the column contents. 3. The 'Manage Columns' menu allows you to manually specify the width for all columns in pixels. Let's explore each of them with an example. In the 'Home' tab, click on the 'Auto fit' icon as highlighted. You can see the options in the dropdown.

## 1. Fit to Header Clicking on the **Fit to Header** option resizes each column to fit its column header.

## 2. Fit to Content Clicking on the **Fit to Content** option resizes each column to fit its content.

## 3. Manage Column Width Manually Click on **Manage Columns**. This menu allows you to enter the width for specific columns in pixels. Enter the column width in pixels for the columns that you want to adjust.

You can use the **Reset** option to restore the column widths to their original values before any changes were made.

## 4. Lock column width After you have adjusted the column widths as desired, you can lock the set column sizes to prevent users from changing them intentionally or unintentionally. Click on the lock icon as shown below.

When the columns are locked, all options for adjusting column and row width are greyed out and unavailable, including the drag and adjust option.

To unlock and enable the column (and row) adjustments, click on **Manage Columns** and the lock icon again.

# Upload/Download Configuration While you can configure the columns manually, if you want to replicate an existing configuration in a similar table, the **Upload and Download Configuration** options let you complete the process in just a few seconds. This is accomplished by downloading the existing configurations to a text file using 'Download Configurations' and then reusing it later for a similar table using 'Upload Configurations'. {% hint style="info" %} When you connect to an existing table in the database, that is already configured, the configuration settings remain intact. Download and upload configuration options are helpful when you have to reset a visual to its original configuration or replicate the same setup in a table with similar fields. {% endhint %} ## Download Configurations 1. To download the existing configuration to a text file, click on **Download Configurations.**

2. In the confirmation pop-up, click Download to download the file to your local system. 3. Click **Save** to save the config file. This will export the column properties in .txt format. ## Upload Configurations To restore a visual to its original column setup or replicate a column configuration from another table, use the **Upload Configurations** option and import the saved configuration file. This includes any added columns as well as the general and display settings. Here’s how to do it: 1. After loading your table into PowerTable, click **Upload Configurations** to import the previously downloaded text file.

2. Select **Click to Upload.** In the file explorer, select the text file you downloaded earlier. 3. Click **Upload.** 4. If conflicting properties exist, a pop-up will appear asking for confirmation to overwrite the existing column configuration. You'll also be prompted about additional columns, such as formulas or multi-select columns (if present), to confirm if you want to add them. You can check the required columns to select them and apply the configurations. 5. Click **Import** to proceed. 6. The columns will be configured and saved automatically with the imported configurations. # Basic Table Operations In this section, we will discuss all the table-editing capabilities offered by PowerTable, such as inserting, updating, deleting, copying, and duplicating rows. This section covers the following topics: * Edit a single cell, a single row, or bulk-edit multiple rows. * Find and replace data * Insert a single row or multiple rows. * Import data from files * Copy, duplicate and delete rows and more. # Edit data With PowerTable, you can edit the contents of your source table. All changes you make are synced with the source in real time. To overwrite any existing data, simply double-click on the cell and enter the new data. In the toolbar, click **Save** to save the changes to the source.

You can also edit a row using the form interface.

# Bulk Edit Many applications only allow for single edits, such as inserting, updating, or deleting one row at a time. However, PowerTable offers the 'bulk edit' feature that enables you to edit and delete values across multiple cells simultaneously. With bulk editing, you can consistently update a common attribute across all entities, ensuring data integrity.

1. To edit multiple cells, select the required cells. To select all the rows in the table or remove the selection, click on the selector at the header. 2. After selecting the rows, click on **Edit** to open the bulk editor. 3. The Bulk Editor pop-up opens where you can specify the new value for the required fields and then click **Apply.** 4. Click on **Save** to save the changes and sync to the source database. In this way, you can edit multiple cells/rows simultaneously using bulk editing. # Find and Replace PowerTable provides the 'Find and Replace' option to find a specific text or value and its occurrences in the table and then replace it with another text or value in one or more instances. * Click on the highlighted icon beside the search bar to open the Find and Replace dialog box.

The 'Find and Replace' dialog box opens.

* Enter the word that you want to search in the 'Find' text box. * In the 'Column' dropdown, select whether you want to search in all columns or only in a specific column. * You can check the options 'Match Case' and 'Match entire cell contents' to find the exact text or value you are looking for that matches the case and/or the entire cell contents.

* Click **Find Next** to locate the first occurrence and subsequent occurrences one by one. Click **Find Prev** to display the previous occurrence.

* Click **Find All** to locate all occurrences of the text/value at once.

* Similarly, click "**Replace"** to replace the selected occurrence and "**Replace All"** to replace all occurrences. #### **Search Options:** **Match Case:** Check this box to find and locate only if the case of the entered text matches the data. **Match entire cell contents:** Check this box to highlight the occurrence only if the entire cell content in the table matches the entered text. # Insert rows Using the **Insert Row** option, you can insert new, blank rows into the table manually. {% hint style="info" %} Use the [**Import**](/explore-powertable/working-with-powertable/basic-table-operations/bulk-import-rows) option to insert or update rows with data from an external file. {% endhint %}

1. Click on **Insert Row** directly to insert a single row. 2. Use the dropdown menu next to the **Insert Row** option to choose whether you want to insert a single row or multiple rows at once. 3. Clicking on **Insert Row** inserts a single row.

4. Click on **Insert Multiple Rows t**o insert multiple records. Enter the number of rows you wish to insert and click **Insert** to add them all at once.

5. You can also insert rows using a form as shown below.

{% hint style="info" %} A maximum of 1000 rows can be inserted at a time. {% endhint %} {% hint style="info" %} You can establish access control to define who can add new rows to the table. To know more, please refer to [this section.](https://docs.powertable.com/explore-powertable/working-with-powertable/basic-table-operations/pages/zDmYsHLaOQ7uXJjBcZUz#id-1.-add-new-rows) {% endhint %} # Bulk Import rows It can be a tedious process to manually insert or update several rows. In such cases, you can bulk import the data directly from an external file.

PowerTable allows you to insert and update multiple rows at once by importing CSV or Excel files. The rows are updated based on matching primary keys from the file. 1. To import new rows, click **Import** on the toolbar that opens the 'Import from file' pop-up. 2. Click on the space to upload an Excel or a CSV file. 3. Select the required file from the systems folder and click **Upload.** 4. You can view the uploaded data. * Records with existing primary keys are updated. * Records with new primary keys are inserted as new rows. * Mismatches are flagged as errors that can be resolved by clicking on logs.

{% hint style="info" %} 1. Ensure the XLSX/CSV file that you import contains the necessary primary key fields and other mandatory fields configured in the 'Columns' section. 2. The maximum number of new rows that can be inserted at once is 20000. {% endhint %} # Delete rows You can select one or more rows in the table and click on **Delete** to delete them.

After clicking on **Proceed** in the confirmation pop-up, the selected rows are deleted. If you have set up an approval workflow, the deletion process will go through this workflow before the actual deletion occurs. You can also choose to perform a soft delete or permanently delete the data, as explained in [this section.](/explore-powertable/access-control/row-permissions#delete-type) {% hint style="info" %} You can establish access control to define who can delete rows in the table. For configuration instructions, please refer to [this section.](https://docs.powertable.com/explore-powertable/working-with-powertable/basic-table-operations/pages/zDmYsHLaOQ7uXJjBcZUz#id-3.-delete) {% endhint %} # Duplicate rows If you have to create similar records, you can duplicate rows and edit a few attributes. 1. Select one or more rows and click on **Duplicate** to duplicate the rows.

You'll be prompted to edit unique primary keys after duplication, as they will also be copied. # Copy rows PowerTable allows you to copy records to the clipboard for pasting into external applications like Excel or Word. Select one or more rows you want to copy and click on the **Copy to Clipboard** icon**.**

# Preview Changes When there are a lot of changes to the table, it can be difficult to track and confirm them before saving them to the database. PowerTable allows you to preview all changes made to the table at once and save or discard them as needed. {% hint style="info" %} Previewing changes is different from the audit log. The [audit log](broken://pages/Qurv5UQ3JnbbDkGCbBHb) provides a comprehensive list of all changes saved to the database, whereas this menu displays changes that have not yet been saved. {% endhint %} 1. The menu bar section on the right displays the total number of changes made to the table.

2. Upon **clicking**, it displays the list of changes, along with the type of changes. You can view all changes by clicking **All**, or select the required tab to view records with a specific type of change.

3. You can click on **Save** to save the changes to the database.

4. The preview screen also allows you to reset the changes you have made. Click on the row that you want to reset and choose **Reset.**

# Explore your data This section covers the following options for exploring data: **Search and filter:** When dealing with a large volume of data, the search and filter options can help you find specific records/details. [This section](/explore-powertable/working-with-powertable/explore-data/search-and-filter) demonstrates how to find and filter the necessary data. **Sort and reorder:** Standard sorting orders, such as ascending and descending, can be applied to both numerical and non-numerical columns to arrange the records in a systematic manner and analyze them. Columns can also be reordered. [This section](/explore-powertable/working-with-powertable/explore-data/sort-and-reorder) covers both. **Show/Hide columns:** [This section](/explore-powertable/working-with-powertable/explore-data/show-hide-columns) describes how to display or hide columns in a table, particularly one with too many columns. **Pin columns:** [This section](/explore-powertable/working-with-powertable/explore-data/pin-columns) explains how to pin and freeze columns in a table. **Column Insights:** Get column summary and essential information to gain insights using the [Insights](/explore-powertable/working-with-powertable/explore-data/column-insights) option. # Search and filter We will now explore the various options in PowerTable for searching and filtering data. These options allow you to analyze data in chunks, especially when dealing with a large volume of data. ## 1. Search The 'Search' option allows you to quickly locate specific records in your table by entering characters, words, numbers, or phrases. PowerTable displays all records containing the entered character, number, or text. 1. Use the 'Search' option to start searching for specific records.

2. Type the character/text in the search box. PowerTable starts narrowing down the matches as soon as you start typing.

3. You can also search for numeric values.

{% hint style="info" %} PowerTable searches across pages if the table spans over multiple pages. {% endhint %} ## 2. Filter PowerTable offers both basic and advanced column-based filtering options in a separate **Filter pane**, allowing you to filter specific data or a range of data based on your defined criteria. Click on the arrows to expand the Filter pane, which includes various column filters.

The filter pane opens, as below:

### 2.1. Basic Filter Using the basic filter option, you can select specific records directly. You can select the 'Require single selection' box to enable selecting only one option at a time.

**'Require single selection' is selected:** Since single selection is enabled in the example below, you can choose only one option to filter the products at a time.

**Searching for a filter criteria** You can use the search box to look for specific criteria to filter by, especially when there are a lot of options.

### 2.2. Advanced filters Use the advanced filtering option to filter based on specific criteria. Click the 'Advanced' tab and specify the condition. The displayed conditions vary based on the data type of the column selected. You can also filter Top N records based on a numeric field. #### 2.2.1. Number filter To filter 'Number' type fields, you can use any of the below criteria highlighted in the image.

You can also use the slider or the text boxes to specify the range and filter the required values.

In the example below, we have filtered the products with prices greater than $3000.

#### 2.2.2. Text filter The following criteria are available to filter text fields.

PowerTable helps you with auto-suggestions even while typing in your criteria.

In the example below, we have filtered products whose color is either black or blue.

#### 2.2.3. Nested filters Add nested filters by applying multiple filter criteria simultaneously. In the example below, we have filtered products that are black in color and have a price greater than 3000.

#### **Clear a Filter** Click on this icon to clear a specific filter.

#### Clear all filters To clear all applied filters at once, click the highlighted icon.

### 2.3. Top N filter The 'Top N' filter option allows you to select the top 'n' records based on any numeric field. Click on the three dots on any field and choose **Top N**.

Enter the number of records to filter. Choose the field you want to filter by. {% hint style="info" %} Click the **Clear filter** icon beside the required filter to clear it or click **Reset All** to clear all the column filters applied. {% endhint %} # Sort and reorder PowerTable provides standard sorting options to sort the records based on specific columns: ascending/descending as well as A > Z / Z > A for numeric and non-numeric columns such as date, numbers, dropdown, text, and more. ## 1. Sort records 1. Hover over the column header you want to sort, then click on the three-dot menu.

2. Choose **Sort Descending** or **Sort Ascending** to sort the records in descending or ascending order, respectively. Let's now sort the 'Product SKU' column in ascending order (A > Z).

The table is sorted as shown below:

Records sorted in ascending order based on Product SKU

3. To sort it in reverse order (in this case, descending order), either click the three dots in the header and select **Sort Descending** or simply click on the down arrow in the header.

4. To remove the sort, click on the arrow again or choose the **Remove Sort** option from the menu.

## 2. Reorder columns You can reorder columns by dragging and dropping the headers to arrange them the way you want.

# Show/Hide columns PowerTable gives you the option to hide one or more columns from the table. To hide a column, hover over its header, click on the three-dot menu and choose **Hide**.

The 'Product Description' column is now hidden.

To display the hidden column(s), click **Show All Columns** in the same menu from any header.

# Pin columns Pinning a column allows you to keep specific columns visible on the left side of a table, even while scrolling the table horizontally. When there are too many columns in the table and you want to freeze a few important ones, you can pin them. When you pin a column, it moves to the left, ensuring that key columns, like IDs or descriptions, remain visible while other columns scroll. * Hover over the column header you want to pin, then click the three-dot menu and select **Pin** to pin it to the left. We will pin the 'Product Name' column here.

The column is now pinned.

* To unpin a column, choose **Unpin** from the same menu on the header.

* You can pin multiple columns as shown below.

* To unpin all of them, select **Unpin All.** All columns get unpinned, returning to their original positions.

# Group Rows By The **Group By** feature allows users to organize and group rows based on a shared attribute, such as category, status, or date. Grouping rows improves readability and simplifies navigation, especially in larger datasets. You can easily collapse and expand individual groups or the entire outline, giving you control over the level of detail you want to see. 1. To group rows based on an attribute, click on **Group By.**

2. Choose the column by which the records should be grouped. In the example below, we are grouping rows based on product subcategory.

3. To view the list of items within a group, click on the symbol '>', next to the required group name. The expanded list is shown below:

4. Within a group, you can create as many sub-groups as possible. Click on **Add Sub Group** to create a subgroup.

5. Choose another column to group them inside the primary group. We will select 'Model Name'.

Grouping by another column within a group

The results are as below:

6. You can use the bin icon to ungroup a particular group. To return to the table, ungroup all of them by clicking the bin icon on all groups.

#### Note: 1. Click on the arrows before the group/sub-group names to expand or collapse them.

2. You can edit the table and perform actions like formatting, bulk-editing, inserting rows, using find and replace, sorting, etc. on grouped rows also.

editing, formatting and inserting rows in a grouped table

# Column Insights The Insights option displays an overview of column values, allowing you to quickly extract key information and insights from column measures and make informed decisions. Assume you are working with a large dataset and need to review any column to determine the number of missing or unique entries or perform a simple categorization analysis. You may want to find the earliest and latest dates in your data, or you may need to calculate the column data's range, average, median, or standard deviation. The Insights option allows you to perform all of these tasks more efficiently while gathering useful insights.

Here are some details you can get: * Sum * Average * Median * Minimum value, maximum value and range * Standard deviation * Number of empty entries/percentage of empty entries * Number of filled-in entries/percentage of filled-in entries * Number of unique entries/percentage of unique entries * Earliest and the most recent date in a date column * Date range in days or months In the example below, we have applied the 'Insights' option to a few columns.

# Conditional Formatting Conditional formatting is essential for directing users' attention to specific performance areas. It allows you to highlight or emphasize certain records using font styles, borders, icons, text and background colors making it easier to identify important data points. The data in the table is formatted according to the user-configured rules. This section outlines how to configure and apply condition-based formatting to a table. You can create your own detailed conditional formatting rules from the ground up. ## Create Rule 1. **Create Rule:** To create a rule, click **Format Rules -> Create Rule.**

2. A side panel opens as shown below:

* **Title:** Enter a title for the rule in the **Title** field. * **Apply to:** Select the column to be formatted in the 'Apply to' field. If you choose 'Rows', all columns will be formatted for the records that meet the condition. * **Style:** Using the **Style** section, you can format text based on color, style, borders, background color, or specific icons. * **Conditions:** In the 'Conditions' section, you can define the conditions using the dropdown options. Based on the type of column (text/numeric) selected, appropriate comparative functions and operators are displayed. * **Add Condition:** You can add multiple conditions by clicking on **Add Rule.** You can then combine them using AND or OR operators. * **Apply:** Click **Apply** to implement the rule or click the Back button to cancel. ## Manage Rules When you have several conditional formatting rules in your report, you can easily view, modify or delete them using the 'Manage rules' option. 1. Click on **Format Rules -> Manage Rules.** A side panel opens and displays all the configured rules. 2. To add more rules, you can click on the **Add new rule** option from this side panel. 3. Click on the highlighted icons below to edit, duplicate, delete, or disable a specific rule. # Layout Views After you've set up your database in PowerTable and begun exploring your data, let's look at how you can view or visualize it for a quick summary and analysis. In PowerTable, you can view your data in four different ways. * Table view * Gantt view * Resource Gantt view * Hierarchy view Each view has capabilities that help you and your team to quickly identify and drill down to the required data. We also provide Gantt and Resource Gantt charts to help you track the progress of activities, milestones, and dependencies. Let's explore them: ## 1. Table View This is the **default view** in PowerTable, where data is displayed in a table format with rows and columns. From other views, you can always return to table view by clicking on **Layout > Table.**

## 2. Gantt View PowerTable lets you use Gantt charts to visualize your task data. If your table contains a list of various activities or **tasks with their corresponding start and end dates**, you can use the **Gantt** option to track their progress. A basic Gantt chart view is shown below:

Let's consider another example. This Gantt view, shown below, displays not only the start and end dates but also the subtasks organized in a hierarchical structure, along with information on progress, dependencies, and milestones.

[This section](/explore-powertable/layout-views/gantt-chart-view) provides a detailed explanation of how to create Gantt charts to track progress and dependencies between activities. ## 3. Resource View When your data is a list of tasks with an assignee column and start and end dates, you can use the **Resource** layout to view the tasks summarized based on available resources, as well as the start and end dates of each of their tasks. 1. Click on **Layout > Resource.**

2. The **Column Mapping** pop-up opens. Select the resource field that you want to use for grouping the tasks, as well as the start date and end date fields from the relevant drop-down menus shown below. Here, we use the field 'QA Assignee' in the Resource drop-down. Click **Apply.**

3. The Resource view opens as below. You can see a list of resources, along with their assigned tasks and start and end dates.

[This section](/explore-powertable/layout-views/resource-gantt) explains editing the resource view properties. ## 4. Hierarchy View As the name implies, this view displays your data in the form of various hierarchical levels. A hierarchical view is possible for data with a parent-child or nested structure, such as an organizational chart, a product catalog, or a classification system. This view can be enabled when a data table contains fields to represent a self-referential relationship (such as a one-to-many relationship in which one data point refers to another within the same table). 1. Click on **Layout > Hierarchy.**

2. The **Hierarchy Layout Configuration** box opens, where you can configure the primary key column and hierarchy column.

3. Select the 'Display Column'.

More information on configuring the hierarchy view is available [here](/explore-powertable/layout-views/hierarchy-layout). The data is shown in the hierarchy view as follows:

The following sections provide a detailed explanation of the various views and how to customize them. # Gantt Chart View A Gantt chart is a **popular project management tool** that displays tasks along a timeline, showing their duration, progress, and start and end dates. Tasks are listed vertically on the left, while horizontal bars (or Gantt bars) extend across the timeline on the right.

The Gantt view enables illustrating each task's progress, duration, milestones, assignees, and task dependencies. With PowerTable, you can create a fully customizable Gantt chart for your data, allowing you to visualize task schedules, identify dependencies, allocate resources, and track deadlines. Beyond visualizing your data, you can easily add new tasks and subtasks, edit timelines, milestones, and dependencies on the fly, and seamlessly scale your data—all while tracking progress through an up-to-date Gantt chart. ## 1. Components of Gantt Chart for Task Management

* **Duration:** The *Gantt bars* represent the duration of the task from the start to the end date across the timeline. * **Progress:** Each bar has a *slider* that shows the task's progress. The multi-level timeline enables you to capture progress at a detailed level. * **Milestones:** Track important events, deadlines, and a process's completion using milestones. **When a task has an end date but no start date**, or **when there is an extra date field to indicate the milestones**, these tasks are indicated as milestones. * **Dependencies:** You can use *connector lines* to visualize project-level dependencies between the tasks. The direction of connector lines helps in indicating the type of dependency, such as **Finish-Start, Start-Start, Finish-Finish, and Start-Finish.** * **Formatting Gantt charts:** Apply conditional formatting to quickly identify status/risks. You can also indicate the critical paths/dependencies to mitigate project risks well in advance. Let's explore how to enable a basic Gantt view for your task table. {% hint style="success" %} 1. To generate a Gantt view, ensure your data consists of at least the 'Start Date' and 'End Date' fields along with the primary key field consisting of task IDs. 2. While PowerTable can automatically identify the data type of each field in the table, you must ensure that these date fields *(Start Date, End Date, Milestone Date)* are assigned the 'Date' data type in the initial table configuration window. 3. The field containing the list of unique tasks should be the primary key. {% endhint %} ## 2. Creating Gantt Chart ### Basic Gantt Let's consider a sample table with a list of tasks with start and end dates and create a basic Gantt chart. 1. Click on **Layout > Gantt.**

2. The **Column Mapping** pop-up opens. * **Start Date and End Date:** Select the start date and end date fields in the relevant drop-down menus as shown below. * **Task ID:** Since the Task ID is the primary key field in the table, it is automatically filled in the Task ID field. * Click **Apply.**

{% hint style="info" %} If you want to create an advanced Gantt chart with additional information like milestones, progress, and dependencies, add these details to the database as additional fields. Then, using the Gantt Layout Configuration pop-up, map these fields to the appropriate dropdown menus. To know more, refer to this section : [Advanced Gantt Chart](#id-3.-advanced-gantt-chart). {% endhint %} 3. The Gantt view appears as shown below. This is a basic Gantt chart.

### Advanced Gantt Chart Let's now create an advanced Gantt chart indicating progress, milestones, and dependencies. In addition to the **start** and **end dates**, the table includes three other fields: * A numeric field to track progress * A date-type field to indicate milestones * A VARCHAR-type field to log connection details. The above components are explained [here](#id-1.-components-of-gantt-chart-for-task-management).

1. Click on **Layout > Gantt** to create a Gantt chart. *If you've already set up a basic Gantt and want to include these additional fields or change a few mapped fields, select* **Layout > Manage Layout**.

2. Assign the fields to relevant drop-down menus to generate a Gantt view.

**Parent ID:** Assign the Parent ID field here, if applicable, to organize tasks in parent-child hierarchical format. For Gantt view, this attribute is optional—assigning this only shows the task list arranged hierarchically. (OR) **Hierarchy By:** If there is no Parent ID field but the data has a hierarchical relationship, you can specify which field to organize the list by. **Milestone:** Assign the 'Milestone' date field here. It should be a date data type. {% hint style="info" %} *While PowerTable can automatically identify the data type of each field in the table, you must ensure that these date fields (Start Date, End Date, Milestone Date) are assigned the 'Date' data type in the initial table configuration window.* {% endhint %} **Progress:** Select the '% Completion' or 'Progress' field here. It is a number field indicating the task progress using numbers. **Dependency:** Assign the 'Dependency' field to capture task connection details. This field uses the format 'TaskID-ConnectionType', for example, 1002FS. Here, 1002 is the task ID, and FS indicates that the current task starts only after task 1002 has finished. Other connection types include SS (Start-Start), SF (Start-Finish), and FF (Finish-Finish). 3. Click **Apply.**

The Gantt view is shown below:

In the absence of a 'Milestone' field, PowerTable can still assign milestones to tasks with no start date but an end date.

Similarly, when there are no progress or dependency fields in the database and you move the slider on any Gantt bar or manually draw connector lines on Gantt, PowerTable generates database columns named '*Progress*' and '*Connect To*' to indicate the task's progress and connection details.

Progress and Connector fields added automatically

{% hint style="info" %} It should be noted that the 'Progress' and 'Connect To' columns created by PowerTable are database columns and will only become a part of the table after a 'Save' or a writeback operation. {% endhint %} In the next section, we'll discuss the customization options available for Gantt Chart. # Resource Gantt # Hierarchy Layout When you are working with multilevel data, you can structure them using the hierarchy layout. PowerTable supports hierarchical layouts for viewing your data in addition to the 'Table' layout. This feature is helpful for use cases where data can be neatly organized into hierarchical structures, such as employee databases, categorical datasets, customer orders, and so on. You can configure a hierarchy layout for table data and then switch between them as needed. Let's take an example of the employee database in the default table view:

The table consists of a list of 100,000 employees. Let us organize them in a hierarchical structure for easier navigation and a clear representation of who reports to whom. **STEP 1:** Click on **Layout > Hierarchy** to set up the Hierarchy layout.

The layout configuration box opens, as below:

Alternatively, you can configure a hierarchy layout first by clicking on **Layout > Manage Layout.** **STEP 2:** Choose the **Primary Key Column.** The *EmployeeID* column here serves as the primary key for the employee table.

**STEP 3:** Choose the **Hierarchy Column.** The hierarchy column is the one containing the data that you want to use to group your records. This column represents the parent level for all records in the table. We have chosen *ManagerID* as the hierarchy column because it not only uniquely identifies all managerial employees, but it also acts as a link between all employees and their managers.

**STEP 4:** Select the column to be displayed in the hierarchy mode as the **Display Column.** This column should be different from the Hierarchy Column. We have chosen the *FullName* column so that the full names of the managerial employees appear at the parent levels in the hierarchy mode. Click **Save.**

The hierarchy mode gets displayed, as below:

* The display column is pinned, allowing you to scroll past other fields. * All employees are neatly organized under their managers, allowing for easier navigation and understanding. * The number of child rows under each parent is displayed alongside it. ### Options available at the parent-level rows

#### Expand and Collapse Levels You can use the arrows to expand or collapse the parent level. Alternatively, you can select the options —Expand and **Collapse**, respectively. Select **Collapse All** to collapse all parent levels in the table. #### Move To Select this option to move a specific record to another parent. Click this option, then choose from the list of parents to which the selected record will be moved. #### Insert Child Row To insert a child row for a record, select the three-dots menu and choose **Insert Child Row.** A new record is inserted where you can enter the details. #### Show Hierarchy Click on this option for any record to see its entire hierarchy. # Access Control Data security and integrity are essential in collaborative environments where multiple users work within a shared space. PowerTable addresses your security requirements by enabling you to configure permissions for rows and columns at a granular level. You can precisely manage who can add, update, or delete rows and columns by specifying their email IDs. PowerTable also lets you set up rule-based access to rows and columns to further customize permissions. This section outlines how to use the **Manage Access** menu to set row and column permissions, allowing or restricting specific users from performing certain actions (add, update, or delete). We'll look at managing both * [row permissions](/explore-powertable/access-control/row-permissions) * [column permissions](/explore-powertable/access-control/column-permissions) # Row Permissions To set row-level permissions to your table, click on the **Set up** tab and choose **Manage Access.**

The 'Manage Access' window opens:

With 'Row Access' section, you can specify users who can add, update or delete rows. ## 1. Add new rows Under the 'Add' section, you can specify who is allowed to add rows in the workspace. Here are the available options: **No one:** Select this option to prevent users from adding new rows to your table.

**All users in this workspace:** Select this option to allow everyone in the workspace to add records.

**Specific users:** Choose this option to allow only certain users to add rows. Enter the email IDs of the users or the Active Directory (AD) groups you wish to grant access.

Click **Save** to apply changes. ## 2. Update existing rows In the 'Update' section, you can specify who has permission to update existing rows. **No one:** Choose this option to prevent users from updating rows.

**All users in this workspace:** Select this option to allow all workspace users to update records.

Allow all workspace users to update rows

**Specific users in this workspace:** Choose this option to allow only certain workspace users to update the rows. Enter the email IDs of the users or the AD groups you want to allow.

**Rule-based access:** Select this option to configure rules that specify the conditions under which a user or an Active Directory (AD) group can update rows in the table. The criteria for updating these rows can be established using column dimensions, allowing for a customized approach to data management and security.

1. Click on **Add Rule** to add a rule.

2. Enter the rule name, the filter condition, and the designated users who can update the rows for these dimensions. Click on **Add.**

3. You can add more filter criteria within a rule by clicking on **Add filter** and then combine them using AND or OR operators.

Adding more than one condition in a rule

4. You can set as many rules as required and assign corresponding users for each.

5. Click **Save** to apply changes. ## 3. Delete rows Under the 'Delete' section, you can specify which workspace users can delete rows. **No one:** Choose this option to prevent users from deleting rows.

**All users in this workspace:** Select this option to allow all workspace users to delete records.

Allow all workspace users to delete rows

{% hint style="info" %} You have two options when allowing users to delete rows: either set up a **soft delete** or let them **remove the rows permanently**. Refer to [this section](#delete-type) to know more. {% endhint %} **Specific users in this workspace:** Choose this option to allow only specific workspace users to delete rows. You can enter the email IDs of the users or the AD groups you want to allow.

Allow only specific users to delete rows

**Rule-based access:** Select this option to configure rules that specify the conditions under which a user or an Active Directory (AD) group can delete rows in the table. The criteria for deleting these rows can be established using column dimensions, allowing for a customized approach to data management and security. 1. Click on **Add Rule** to add a rule.

2. Enter the rule name, the filter condition, and the designated users who can delete these rows. Click on **Add.**

3. You can add more filter criteria within a rule by clicking on **Add filter** and then combine them using AND or OR operators.

4. You can set as many rules as required and assign corresponding users for each.

5. Click **Save** to apply changes. ## Delete Type With the 'Delete Type' option, you can configure whether users can delete rows permanently or do a soft delete.

**Permanent (Hard Delete):** When you select this option, the deleted rows are removed from the table or database permanently. **Mark as Deleted (Soft Delete):** When you select this option, the deleted rows remain in the database but are marked as deleted. This process involves setting a specific value to an existing column of the table when a row is deleted. For instance, we assign the value 'DELETED' to the 'Status' column for the rows that have been deleted.

The deleted rows in the table look as below, after saving your changes:

**Note:** In the above example, a new column called 'Status' has been created using the 'Add Database Column' option to track deleted rows. #### **Show Deleted rows** While soft-deleting rows, if the 'Show Deleted rows' option is selected, the deleted rows are shown as above in a grayed-out format. When this option is unchecked, the deleted rows are hidden from the table.

You can use the audit log to keep track of all changes, including the list of permanently deleted and soft-deleted rows.

In addition to row-level access control, you can also set up approval workflows so that every change made by a user goes through a review and approval process. This is explained in [this section](broken://pages/TKIqzng0msYl6vYq23R7). # Column Permissions Just as you can set row permissions, you can also establish column permissions. By default, all workspace users who have access to the PowerTable item can view and modify all columns. Access control for each column can be configured through the 'Manage Access' menu. Here, you can clearly specify who is allowed to write values to each column. To set up access permissions to the columns, 1. Click on the **Manage Access** menu from the **Setup** tab**.**

2. Choose **Column Access** tab.

3. To set permissions for a column, enter the name or email ID of the users to whom you wish to provide the access in the 'Who Can Edit' field. In the example below, we are setting access control for the 'Product SKU' column by specifying the users' name.

4. You can enter as many users as you want. You can also type in the email ID of your organization group or active directory list.

Access control list who can edit 'Product SKU' column

5. You can uncheck the 'Allow Edits' checkbox to prevent users from editing specific columns. Typically, the primary key field is locked from editing. Click **Save** to save the settings.

# Master-Detail Tables (Parent-Child Linked Records) Master-detail tables are used to represent a one-to-many relationship between two entities. In this relationship, the data in one table (the **master** table) is related to multiple records in another table (the **detail** table). Also known as **parent-child linked records**, this feature establishes a hierarchical relationship between two sets of records by connecting one record (the parent) to one or more related records (the children). This structure enables logical data grouping and is used to represent a master record along with its associated details. For example, there could be a list of *customers* (parent records), each with several *orders* (child records).

#### **Why Use Master-Detail Tables?** * **Organized Data Structure:** Group related records (such as orders and line items) for easier access and comprehension. This is especially useful when you are working with complex datasets. * **Ensure Data Integrity:** Link all child records to their parent records to prevent inconsistent data entries that lack context. (e.g., an order item with no associated customer) * **Streamlined Data Entry and Update:** Child records inherit values from their parents, making data updates easier and reducing duplication. For instance, you can add a common field to the master table instead of updating all child records. * **Simplified Reporting & Filtering:** You can drill down and filter reports easily based on parent-level criteria. * **Improved User Experience:** Master-detail view enables you to easily manage related data on a single screen. #### Here's a breakdown of the key components: **Master Table:** The master table contains unique records and serves as the main source of information. Each record in the master table represents a single entity or object. For example, in a product store database, the master table could be the 'categories' table, with each record representing a different product category.

**Detail Table:** The detail table has related records that connect to one record in the master table. This establishes a link between the master table and its associated data. For example, the detail table could be the 'products' table, where each record represents a product belonging to one of the categories in the 'categories' table, as shown below.

**Foreign Key Column:** The relationship between the master and the detail table is established through a common column known as the **foreign key**. The foreign key in one table (the detail table) references the primary key of another table (the master table). In our example, each record in the *products* table refers to its associated parent record in the *categories* table using the foreign key **CategoryID**.

The master table and detail table typically have a one-to-many relationship. This means that a single record in the master table can be linked to multiple records in the detail table, whereas each record in the detail table can only link to one record in the master table. In the following sections, we'll look at how to create parent-child linked records and view the data in master-detail view. # Creating Master-Detail Tables If two or more tables in a dataset share a foreign key column (relation column), a master-detail relationship can be established between them. Establishing a master-detail view allows you to easily organize, scale, and maintain your data. In our example, we have two tables: the *'categories'* table and the *'products'* table. They share a foreign key column, the '*Category ID'* column.

We can also use a [lookup column](https://docs.powertable.com/explore-powertable/master-detail-tables-parent-child-linked-records/pages/PCbjP6zrTFcrIPrKHx1g#id-3.-lookup-table) in the *products* table for the **CategoryID** field; this lookup column retrieves the corresponding category names from the *categories* table (key: CategoryID, value: CategoryName).

Both tables look as shown below. Let's now create a master-detail relationship between these tables.

In the master table, you need to add a **relation column.** This column connects both tables using the foreign key. **STEP 1:** To add this column, go to **Setup > Columns.**

**STEP 2:** In the 'Columns' window that opens, click on **Add Visual Column > Add Relation Column.**

**STEP 3:** Configure the required details as below: * **Type:** Select the column type. We chose the 'Text' type because we wanted to display the category name. * **Column Name:** Enter the name you wish to use for the new column.

#### Configure Relation Table Enter the details of the child table that you want to connect to. To establish a connection to the *products* table, let's input the following details: **Connection**: The name of the connection used to fetch the child table. **Schema:** The name of the database schema where the table is available. **Relation Table:** Select the necessary child table to connect to.

#### Configure Relation Columns **Column from Current Table:** Choose the foreign key column from the **current master table** as the relation column. **Column from Relation Table:** Choose the foreign key column from the **relation table** as the relation column. This is the column that connects both tables.

#### Configure Label Column **Column To Display:** Choose the label/display name that will appear when you drill down a record to its details table.

STEP 4: Click **Save** to save the configuration. The relation column is added.

The master-detail relationship is now established between the tables, as shown below:

# Approval flow PowerTable can connect to your database live for real-time data updates and synchronization. While row and column-level access permissions ensure data integrity, you can go one step further to secure your data by setting up approval workflows. In a team, users might need management's approval before making any data changes to ensure correctness and accuracy. There can be scenarios like multiple users accidentally changing the same data, entering incorrect values, etc. All these can be streamlined by setting up an approval workflow process in PowerTable. ## 1. Approval flow sequence Whenever a change is made, :ballot\_box\_with\_check: Approvers receive an email about the change request with a link to the table. :ballot\_box\_with\_check: Changes are indicated in the table based on how they have been set to be handled. :ballot\_box\_with\_check: Approvers review the log in the 'Review' tab and choose which changes to approve or reject. :ballot\_box\_with\_check: Author is notified via email about approval or rejection. :ballot\_box\_with\_check: Approver can also propose changes to the author. :ballot\_box\_with\_check: Author implements or skips the suggested changes and sends it again for review. :ballot\_box\_with\_check: Approved changes are synced to the source. This section explains all of the above in detail. ## 2. Steps to set up approval workflow 1. [Enable Approvals in PowerTable](#id-3.-enable-approvals) 2. [Select a method to choose approvers](#id-4.-select-approvers) 3. [Set up the way to handle the changes](/explore-powertable/approval-flow/handling-changes-before-approval) Once enabled, every change made by users undergoes an approval process before getting saved and synced to the source. ## **3. Enable Approvals** You can turn on the approval workflow by following the steps below: 1. Click on **Approvals.**

2. Click **Configure Approvals.**

3. Enable approvals by toggling on the highlighted button.

## 4. Select Approvers The next step is to select the approvers who will review and approve any changes made to the table. There are three ways to select approvers. 1. [**User Approvals:**](/explore-powertable/approval-flow/user-based-approvals) Manually entering the email IDs of approvers or IDs of AD groups (Active Directory Groups). 2. [**Rule-based Approvals:**](/explore-powertable/approval-flow/rule-based-approvals) Configuring a rule-based system to identify the approvers. 3. [**Approval using a Lookup Table:**](/explore-powertable/approval-flow/approval-using-a-lookup-table) Using a lookup table to establish the rules. A detailed guide to each method is available in the upcoming sections. # User-based Approvals ### Enter the list of users who can approve This is a straightforward method where you will manually specify individuals who will act as approvers. 1. Choose the option— **Specific users.** 2. Select specific users in the workspace by typing their names or email addresses who will be responsible for reviewing and approving the changes. 3. Click **Save.**

# Rule-based Approvals ### **Identify Approvers based on configured rules** Define approvers for specific columns or records in the table based on set rules and criteria. Enter the rule name, the filter criteria, and the designated approvers for that rule. You can set as many rules as required and assign corresponding approvers for each. Note that rules are executed in the order they are added and can be reordered. The detailed steps are as follows: 1. Select the highlighted option below.

2. Click **Add Rule** to add a rule.

3. Enter a name for the rule, filter criteria, and approvers for the criteria.

4. Click on **Add filter** to add multiple filters/criteria within a rule and combine them using AND or OR operators as below:

5. To delete a filter criteria, use the bin icon.

6. Click **Create.**

7. You can now find the rule added to the list of rules. You can add more rules by selecting **Add Rule.**

8. **Default Approver**: In the final step, add one or more default approvers who will approve the changes that do not meet the above-configured rules/criteria. Click **Save**, and you are good to go.

Approver for other changes that do not meet these rules

# Approval using a Lookup Table ### Get the approver details **from a Lookup Table** In this method, you can select the approvers based on rules, by looking up data from another table.

Users that meet rules from a lookup table

The options are explained below: * **Lookup Table**: This dropdown menu displays a list of tables in the connection. Select the table from which the approvers list is to be looked up/fetched. * **Column Containing Approvers**: Choose the column containing the approvers' email IDs.

The next step is to map the matching columns from the lookup table to their corresponding columns in the source table to join the tables. * **Source Table Column to apply filter:** Select the column from the source table that is also present in the lookup table. * **Matching Lookup Table Column:** Map the matching column from the lookup table. * You can set multiple conditions (join multiple shared columns) as needed, by clicking on **Add Condition.**

In the above example, each subcategory in the lookup table has a person-in-change with their Email IDs added in the 'Approvers' column. By mapping the subcategory name columns from both tables, we allow the approvers' email IDs to be fetched for each subcategory. * **Default Approvers**: Specify the default approvers to approve changes that do not involve the above-set conditions (subcategories with no approver details). Click **Save.**

* Click on **Preview Rules** to view the approvers list for each subcategory. * You can use the **Sync Rules** option to sync the connected lookup table to fetch the latest data. The last sync details and the status can also be seen. # Multi-level Approvals Multi-level approvals require a change request to go through multiple levels of review before it is finally approved, ensuring compliance, oversight, and better decision-making. Each level corresponds to a different approver who reviews and approves the request at that level. You can create up to three levels of approval using PowerTable. Let us look at the steps to enable multi-level approvals. 1. Enable multi-level approvals by toggling on the highlighted button. 2. Configure the required number of approval levels. 3. Enter the name of the approvers for each level. 4. Click **Save.**

# Setting Additional Notification via MS Teams By default, approvers are notified through emails about the change requests. Optionally, you can also use Microsoft Teams channels to notify the approvers. 1. Click **Add Channel** to add a Microsoft Teams channel.

2. Configure the channel and webhook details as shown below:

3. Click **Save** to add the channel. Change requests will now be communicated to the approvers through email as well as this channel.

# Handling Changes Before Approval In this section, you can specify your preferences for saving changes to the databases, when an approval workflow is in place. There are two options available. ## **1. Only after approval** In this method, the changes are saved to the database only after approval. This is the default mode.

After making changes, click **Submit for Review.**

This opens the pop-up below, where you can add the description, due date for approval and priority of the change. Click **Submit** to send them to the approver.

## **2. Save with Pending Approval Status** In this option, the changes are saved and synced to the database with a 'Pending Approval' status flag. The rows marked as pending are sent for review. Once approved, the status column will be updated to reflect the 'Approved' flag, and the data will also be updated accordingly.

1. Choose any existing column from the table to reflect the status flag field. We have used the column 'Status' in the example below, where the status is recorded. 2. Enter the required text to be displayed for each state, such as 'pending review', 'approved', and 'rejected'. 3. Click **Save.** After making the changes to the table, click **Submit for Review**. This saves the data along with its status. The saved changes are displayed with the label, 'Pending'. Approved and rejected changes are displayed with the labels, 'Approved' and 'Rejected' respectively.

# Review Changes - Approval process In this section, we will discuss the approval workflow process from both the author's and the approver's ends. The author is the user who has requested changes, and the approver is the user who approves them. Let us assume the author has made a few changes and has sent them for approval.

Now we will go to the **Approvals** tab where we can find the outline as well as detailed information on the change requests.

It has two sections: **Pending Requests** (requests you need to approve) and **My Requests** (requests raised by you).

{% hint style="info" %} In certain situations, a user may serve as both an approver and an author, depending on the set rules for specific sections or departments. In this case, the user can easily switch between tabs to view their pending approval requests as well as the requests they have submitted. {% endhint %} Let us start with the 'My Requests' tab. ## 1. 'My Requests' screen This tab displays an overview of the requests and changes you have made and sent to the approver.

This screen has the following sections: * **Pending Review:** This tab lists all your open requests that are yet to be reviewed and approved. * **Overdue:** This tab displays your open requests that have crossed their due date for approval. * **Action Required:** This tab displays any adjustments/suggestions made by the approver for the user to act on. * **Closed:** This tab displays requests that have been approved or closed. You can switch between these tabs. Once the requests are approved/closed, they will be moved to the 'Closed' section. **Search:** This window has a search box that helps you search for specific requests.

## 2. Detailed view of your request The 'My Request' screen provides an overview of all change requests submitted. To understand each change request in detail, you can click on **Details.**

It includes the request ID, description (if you have added one), approver information, the date and time it was raised, who raised it, whether it is a high priority, the due date for approval, and its status. * Click on **Edit Details** to change any of these details.

It also offers a number of options, including editing data, adding comments, viewing history, closing the request, and re-submitting it for review.

Options available in the 'My Requests' tab

### 2.1. Add or View Comments It has a 'View Comments' option that lets you view existing comments and also add new comments to the changes so that approvers can understand them better. This works similarly to the [commenting and collaboration](/explore-powertable/collaborative-commenting) feature. 1. Click on the cell to which you want to add the comment. The comment icon is enabled.

2. Click on it to add a comment and then post**.** The comments are updated automatically and approvers can view them when they open the requests.

3. You can also add assignees and start a comment thread. To add assignees, click on **Assign to user** and select the assignee, or you can directly use an @mention and add their names in the comments.

The task status is automatically set to 'Open' and an email notification is sent to them. The assignees can reply to the thread to start a conversation. 4. Click on **View Comments** to view all the added comments.

### 2.2. Edit 1. To edit the data in the submitted changes, click on the **Edit** option. The changes can be a new one or the undoing/modification of the existing ones.

2\. After editing, click **Save.** The saved changes are submitted automatically.

### 2.3. View History Click on **View** **History** to view the history of edits or user actions. ### 2.4. Close Request Use the **Close Request** option to pull back the submitted request and move it from the 'Open' to the 'Closed' section.

### 2.5. Re-Submit For Review If the approver has requested changes during their review, you can either make the suggested changes or choose to skip them while adding a comment. Once you have made the necessary changes or added your comment, click the **Re-submit for Review** button to resubmit your work.

## 3. 'Pending Requests' window This tab shows all the pending changes that require your review and approval. If you are an approver, you can view the requests submitted by other users in this section. Similar to the 'My requests' tab, this window has options to **search** for specific requests. ## 4. Detailed view of a pending request Click on the **Details** option for any request to access the detailed information. The approver can access this window to view detailed changes in a single request. They can view the changes, which are highlighted in blue, and deleted rows in red as below: 1. To approve a change, select it and click **Approve.** 2. To reject a change, select it and click **Reject.** 3. To request/suggest changes, click **Request Changes.** The steps are explained [here](#id-5.-request-changes-or-adjustments). 4. Click on **View Comments** to view added comments. 5. Select **History** to view the history of actions performed. ## 5. Request Changes or Adjustments The approver can suggest modifications by requesting changes. In response, the author can review these suggestions, make the necessary adjustments, and then resubmit the revised request for further consideration. 1. If you are an approver, you can request changes to the author by clicking **Request Changes.** 2. You can optionally add comments describing the suggestion. Comments help the author understand the modifications you need. Click **Submit.** 3. The author is notified through email. They can make the changes and [resubmit it for review](#id-2.5.-re-submit-for-review). Both the author and the approver are notified through emails about the suggested changes and the resubmission, facilitating quicker and easier collaboration through a simple conversation thread. ## 6. Removing Approval Workflow To disable the approvals, click on the **Manage Approvals** tab and toggle off the Enable Approvals button, and click **Proceed.**

{% hint style="info" %} When approval flow is disabled, all active requests are deleted, and any new changes to the table can be committed directly by clicking on **Save**. {% endhint %} # Automation When working with data in a collaborative environment, it is necessary to notify team members and stakeholders about changes to the data. Additionally, it is important to propagate these updates to related and dependent tables to maintain data consistency and integrity. PowerTable can automate these types of repetitive workflows by integrating with necessary applications based on defined actions and conditions. You can create automated workflows that execute one or more predefined actions in response to specific triggers. Choose when your automation should run by defining triggers, and then add one or more actions with required customizations to define what happens next—all without the need for coding. ## 1. Understanding automation workflows An automation workflow consists of a **trigger** and **one or more actions** that follow it.

Triggers can be any event, such as data updates, row insertion or deletion, a configured time schedule, a form submission, or a click of a button.

The actions that you can define include: * Sending real-time notifications to the stakeholders about the data changes across different channels (email, Microsoft Teams message, webhooks) to keep them informed. * Cascading the data changes across all related and dependent tables consistently.

PowerTable can automate such repetitive workflows by executing these predefined actions based on triggers (events or data changes). ## 2. Setting up a Workflow Setting up an automation workflow involves the following steps: 1. **Create an Automation Workflow:** Create a new automation workflow to configure the trigger and action(s). 2. **Define the Trigger:** Configure the events or data changes that will trigger your workflow. E.g., a new record is created in the table, an existing record is updated or deleted, etc. You can also add one or more conditions to the trigger event to make it more specific. 3. **Choose Actions:** Select the actions you want to perform in response to the event. E.g., create a new row in another table, send an email or Microsoft Teams notification, post the update through webhooks, etc. When an event occurs, PowerTable can automatically: * **Send Notifications**: Trigger email or MS Teams notifications to keep stakeholders informed. * **Trigger Webhooks**: Integrate with external systems by sending real-time data updates. * **Perform Cascaded Updates**: Apply changes across related records to maintain data consistency. The upcoming sections in the guide provide an overview of how to configure and manage automated workflows in your environment. # Send Mail notifications PowerTable can send email notifications to relevant stakeholders whenever an event or data change occurs. 1. Select **Setup > Automations.**

2. Create a new automation workflow by clicking on **Create**, as shown below.

3. Click **Add Trigger** or directly select any of the suggested triggers to configure the triggering event.

4. Let us assume you need to notify your team members whenever a new record is created. In this case, you can select the trigger, 'When a record is created'. A trigger event is added.

{% hint style="info" %} You can add multiple conditions and combine them using AND/OR, for an event to initiate the action. {% endhint %} 6. Click **Add action** and then choose **Send Email** to send an email notification whenever the event occurs.

7. Enter the action description, the email addresses of the members to be notified, the subject, and the body of the email. Finally, click **Save Changes**.

8. The automation workflow is now set up successfully. You can use the pencil icon to name this workflow for easier identification. You can also add more actions to this event, such as triggering a webhook or cascading updates.

# MS Teams notifications Send notifications across Microsoft Teams channels whenever an event or data change occurs. 1. Select **Setup > Automations.**

2. Create a new automation workflow by clicking on **Create.**

3. Click **Add Trigger** or directly select any of the suggested triggers to configure the triggering event.

4. Let us assume you need to notify your team members whenever a new record is created. In this case, you can select the trigger, 'When a record is created'. A trigger event is added.

5. You can define additional properties and add conditions to further customize your trigger and make it more specific. In the image below, we have added a trigger description and a condition for the trigger that specifies a particular category. {% hint style="info" %} You can add multiple conditions and combine them using AND/OR, for an event to initiate the action. {% endhint %}

6. Click **Add action** and then choose **Microsoft Teams Message** to send a Teams message whenever the event occurs.

7. Enter the action description and the message to be sent. Then, configure the respective Teams Channels as shown below. Finally, click **Save Changes**.

8. The automation workflow is now set up successfully. You can use the pencil icon to name this workflow for easier identification. You can also add more actions to this event such as sending an email or cascading updates.

# Webhooks PowerTable also supports adding a webhook URL to post the event or data changes. Any changes made to the table by users, which includes CRUD operations like insert, update, and delete, can be instantly posted from PowerTable to a specific endpoint. You can also schedule regular notifications. {% hint style="info" %} Triggering webhooks involves the same initial steps as sending an email or an MS Teams message. Follow steps 1 through 5 from the [previous section](/explore-powertable/automation/ms-teams-notifications) to create an automation and add a trigger. {% endhint %} 1. Click **Add action** and then choose **Trigger Webhook.**

2. Configure the webhook by adding the webhook URL and authentication details as shown below. Click **Save Changes.**

3. The automation workflow is now set up successfully. You can use the pencil icon to name this workflow for easier identification. You can also add more actions to this event, such as sending an email or cascading updates.

# Cascade Updates When updating a database, it is essential to also update all related and dependent records to maintain data consistency, accuracy, and integrity. PowerTable allows you to automatically cascade updates across all related tables using an automation workflow. 1. Select **Setup > Automations.**

2. Create a new automation workflow by clicking on **Create.**

3. Click **Add Trigger** or directly select any of the suggested triggers to configure the triggering event.

4. Let us assume you need to update relevant records in another table whenever a new record is created. In this case, you can select the trigger, 'When a record is created'. A trigger event is added.

5. You can define additional properties and add conditions to further customize your trigger and make it more specific. {% hint style="info" %} You can add multiple conditions and combine them using AND/OR, for an event to initiate the action. {% endhint %} 6. Click **Add action** and then choose the required database action that you want to perform. Let's update a record in another table whenever a new record is created in the current table.

7. Add the connection information, schema, and table name for the table you want to update simultaneously. You can specify any conditions, such as updating only certain records in the table.

Update table details and conditions to update

8. Add the required row values to be updated in the table.

9. Click **Save Changes** after configuring the row values.

10. The automation workflow is now set up successfully. You can use the pencil icon to name this workflow for easier identification. You can also add more actions to this cascading update event, such as sending an email or a Microsoft Teams message.

# Pause an Automation After creating automations, you can pause the required workflows without deleting them. This allows you to enable or disable one or more workflows as needed, saving you from having to start over if you need them again. To pause or resume an automation, simply click the toggle button next to it.

To delete an automation, click the three-dot menu next to its name in the list and select **Delete**.

# Webhooks PowerTable allows you to instantly send transaction results to a specific URL endpoint. This is accomplished by including a webhook URL in your PowerTable app. The transaction logs contain information about any database changes made by users using PowerTable, including CRUD operations such as Insert, Update, and Delete. It displays detailed information about the transactions, such as the transaction type, number of rows affected by the transaction, status and duration of the transaction, the person who initiated them, etc. You can view the transaction logs from the PowerTable console, as detailed in [this section](/explore-powertable/powertable-console/transaction-logs). If you want PowerTable to immediately send these transaction logs as notifications to a specific endpoint, you can set up a webhook as explained below: * Click on **Setup >** **Table** **Settings > Webhook** to access this window.

* Enter the endpoint's URL in the 'Post URL' text box. * You can also add your required request header by clicking on **Add New Header** and entering the key-value pair.

# Type 2 SCDs Type 2 Slowly Changing Dimension (SCD) is a data warehousing method used to track changes in data over time by preserving the change history. You can view the historical data for each record and their corresponding time range when it was valid. ### 1. Understanding Type 2 SCDs * Whenever an entity's data in any dimension changes, a new row is created instead of overwriting the old ones. * Each new row includes the updated information along with the start date and end date that indicate the validity period of the updated information. * There is an additional flag column that indicates the record that is currently active. This column can be of the checkbox type or a simple true/false or 0/1 column. * There can be one or more timestamp columns in addition to the start date to signify when a new record was actually created or when the change was made effective. This approach allows us to analyze the historical data and understand how the information has evolved over time. ### 2. Example for Type 2 SCDs Let us consider an employee database where the employees' details, such as salary, incentive, job role, etc., can slowly change over time. With SCDs, whenever there is a change, a new row with the updated details is created instead of overwriting the old one. This new row includes an effective start date that indicates when it was updated or when the new details became live. Meanwhile, the old row will have an end date marking when it was last valid. Likewise, every change made to an employee is tracked through a series of rows, each with its respective start and end dates. Find below the database where SCDs are in place:

ID (Surrogate Key)	Emp.ID (Business Key)	Employee Name	Salary	RowValidFrom	RowValidTo
1	C501	John S	70000	20-10-2023	23-10-2024
56	C002	Alan B	75000	01-01-2024	31-12-2025
234	C501	John S	80000	24-10-2024	31-12-2025
235	C988	Lily S	65000	21-10-2024	31-12-2025

One table row represents an entity's attributes for a defined timespan. The whole lifespan of one business entity can be inferred from a list of such rows. In the next section, we will discuss how to configure a database that can support SCDs and how you can assign the appropriate keys in PowerTable. # Configuring Type 2 SCDs in PowerTable Type 2 SCDs can be optionally added in your PowerTable app to track changes in your database. Before enabling Type 2 SCDs, please note that they can be configured for specific tables that have the necessary fields. In this section, we will go over how to configure them from start to finish. ### 1. Configuring the Database To set up SCDs, the source table must contain a few specific column types to enable and support them. After adding them, we can configure the columns and the appropriate keys in the PowerTable app. In this section, we will look at all of these steps in detail. We will use the below table to demonstrate the implementation of Type 2 SCDs in PowerTable:

{% hint style="info" %} The *ID\_New* column is assigned in the **Primary Keys** field, while other columns are assigned to the **Columns** field, in the Visualizations pane. {% endhint %} To configure SCDs, your table must contain the highlighted columns as shown below. The Future/Effective date column is optional.

These are explained below in detail. ### 2. Key Terms used in Type 2 SCD implementation Let's revisit some of the key terms used while configuring SCDs. Every row in the table would have a business key and a surrogate key. #### 3.1. Business Key 1. The business key/natural key is an entity's unique identifier. In the above example, each employee can be viewed as an entity identified by a unique ID (*Employee ID*) known as the business key. 2. It must be unique to an entity, mandatory, and non-null. 3. It is an attribute of the entity derived from the data. As shown in the above example, your table should have a numeric/text/alphanumeric column that can be used as a business key and meets all of the above requirements. #### 3.2. Surrogate Key * The surrogate key is a system-generated key and identifies each unique record in the table. * Unlike business keys, surrogate keys are not derived from data and are not attributes of entities. * A surrogate key is automatically incremented by one whenever a new record is created to track the change. By default, PowerTable selects the primary key as the surrogate key. Therefore, the table must include a numeric data type column that has a unique value for each record. This column should be assigned to the 'Primary Keys' field in the Visualizations pane so that EDITable can automatically detect it as the surrogate key. {% hint style="info" %} It’s important to note that while an entity's business key can be identical or non-unique across rows in the table, the surrogate key must be unique for each row. {% endhint %} #### 3.3. Start Date This date indicates the day when the updated information became active or when changes were made to the table. #### 3.4. End Date This date represents the last day until that row's information is valid. #### 3.5. Active Flag This is an additional flag column to signify the record that is currently active. This column uses a simple indicator (0/1 or true/false) to denote whether a specific record is presently active or a valid version. The table should contain a VARCHAR/text type column for setting up an active flag column with the default value 'true' set for all rows. You can then configure this column in the 'Manage Columns' window with the checkbox type. #### 3.6. Future Date/Effective Date EDITable lets you add an effective date/timestamp column alongside the start date column to record when the change actually took effect. To do this, create another date column in your table and then choose it from the 'Future Dates' dropdown menu. {% hint style="info" %} * The **Start Date** indicates when the record was added/updated and became valid in the system (operational perspective). * The **Effective Date** represents the actual date when the change took effect in the real world (business perspective). * These two dates are usually the same in most cases, but they can differ due to internal policies, business rules, delays in data entry, processing, etc. Adding the Effective Date allows you to query historical data based on when the change was valid in the business context, not the system context. This enables accurate tracking that can be helpful for reporting, compliance audits, and ensuring downstream systems use the correct business timeline. {% endhint %} ### 3. Configuring the Columns After configuring the source table as explained above, we have to ensure the columns are assigned the appropriate properties to support Type 2 SCDs. This is done in step 2, in the 'Manage Columns' window, after loading your table and configuring the source.

Best practices for configuring the columns in the PowerTable visual: * Ensure that the surrogate key is assigned as the primary key in the Visualizations pane. * Enable the 'Required' option for the above fields so that these are always available. * Disable 'Allow Edits' for the surrogate key to avoid accidentally editing it. This ensures that each surrogate key is distinct and helps to identify the row. * Ensure to choose appropriate 'Input Type' for the columns (Date type for Start Date, Effective Date and End Date, Check box type for Active Flag column, etc.).

* After configuring the columns, click **Save** to save the configuration. {% hint style="info" %} You can always return to this window and reconfigure the columns if necessary by selecting the **Manage Columns** option in the Home tab. {% endhint %} The next step is to enable Type 2 SCDs. ### 4. Enabling Type 2 SCD support In the next step, you can enable Type 2 SCDs. 1. Click on the link as shown in the image below:

2. Enable support for SCDs using the toggle button as shown below and start configuring them.

### 5. Configuring SCDs Now that we have created a table with all of the necessary fields and how to use them, we will begin adding them to the pop-up window, as shown below:

Click on **Save** to save the choices. **Close Settings** to return to the table.

The table now looks as shown below with all active records.

### 6. Accessing Row History Let's assume a few records in the table have been edited. To view the change history of the record, you need to follow the steps below: 1. **Select the record**: Choose the record you are interested in. 2. **View History**: Once you select the record, click on **History** to view its history. This history includes all the changes that have been made to the record over time.

3. **Most Recent First**: The changes are displayed in order, with the most recent changes at the top. This way, you can quickly see the latest updates first. The history of the particular record is shown below:

{% hint style="info" %} At a time, you will be able to view the history of a **single row.** {% endhint %} This way, you can set up Type 2 SCDs for your database. {% hint style="warning" %} Note: 1. The action of configuring SCDs is permanent and cannot be undone once enabled and saved. 2. If you have missed configuring SCDs and wish to set it on a later date or you need to edit the existing configuration, you will need to **reset** all the visual settings by clicking on **Reset** in the **Settings** window. Note that this resets all the settings, including the source and column configurations. {% endhint %}

* To reset the SCD configurations, or to enable SCDs at a later date, you need to click on **Reset.** * To view the existing SCD configuration, you can go to **Settings -> Slowly Changing Dimensions.**

{% hint style="info" %} The following features are not supported if you have enabled Type 2 SCDs for your table: * Soft delete * Audit log * Persistence mode in approval workflows {% endhint %} # Forms Forms allow you to collect data directly from users and input it into your database. This section explains how to collect user data using forms and store it in your PowerTable app. Forms are a versatile tool that can be used for various purposes, such as capturing expense reports, collecting user responses, running polls and quizzes, gathering customer feedback, and more. You can use them for tasks like surveying, registering event attendees, managing internal requests, and onboarding new users. They make data collection easier and optimize workflows for both individuals and businesses with their structured format and customizable fields. ## 1. Create a Form 1. Go to **Setup > Forms.**

2. Click on **Create a Form.**

You'll see a window where you can configure the required fields for your form and customize them.

The primary key field is automatically added to the form since it is mandatory. You can start adding other required fields to your form.

## 2. Add New Fields 1. Hover your mouse down the primary key field and click the '+' icon to begin adding new fields below.

2. Select the required fields from the dropdown list or click 'Select All' to select all the table fields to add to the form.

3. You can also add more fields later by clicking the '+' icon directly below any previously added field.

## 3. Create Field Groups To make user inputs more readable and organized, you can group related or similar fields together. In address blocks, for example, you can group related fields such as house number, street name, city, state, zip code, and so on. Grouped fields also enable you to perform bulk operations on fields, such as hiding them or formatting them at once. 1. Click on **Add Group** to create a new group.

2. A new field group is created, within which you can begin adding fields. 3. When adding a new field, click the '+' icon on either side of the existing field to group it together.

You can specify which fields are mandatory and which are optional. After configuring all the required fields in the form, click **Save.** Your form is now created. # Audit logs The audit log is a comprehensive record of all changes made to the table. PowerTable's audit log is interactive and offers both search and filter options. It also allows you to download and export the logs. This section provides information on how to access audit logs and other related features. ## 1. Track Changes Click on the **Audit** tab to open the audit logs window.

The audit log displays detailed information about recent changes and updates to your table, keeping track of all modifications. The changes are classified into different tabs—Data, Access, etc.—based on the type of change. #### Data The **Data** tab provides a chronological record of transactions and actions to track all data changes in the source table, as shown in the image below.

This log includes the row ID of the modified record, details of the modified columns, the previous and new values, the person who made the change, the date and time of the change, and the type of modification. {% hint style="info" %} The audit log captures all the significant actions performed on the table, including Insert, Update, Delete, and Soft Delete. {% endhint %} #### Access The Access tab keeps track of any changes to row and column access permissions, including who made the changes and when they were made.

Similarly, the **Settings** tab lists all changes to table settings and so on. ## 2. Search and Filter You can use the 'Search' and 'Filter' options to look for specific changes made to a specific record.

The options available are as follows: 1. **Search Row ID -** Enter the Row ID in this search box to search and track all changes made to a specific row. Based on the row ID, the logs are dynamically filtered and displayed. 2. **Action -** You can filter records by their type of change, such as inserted, updated with a new value, deleted, or soft-deleted. 3. **Approved By -** When an approval system is in place, you can use this option to filter changes based on who approved them. [This section](broken://pages/TKIqzng0msYl6vYq23R7) contains more information about the approval workflow. 4. **Modified By -** Use this filter to see changes made by specific members of your team. 5. **Last 30 Days -** Use this filter to see changes made within the last 30 days or in a specific date range. 6. **Reset All -** Select this option to remove all applied filters. ## **3. Export Logs** The **Download Logs** option allows you to download and export audit logs to Excel or CSV files or email them.

# Collaborative Commenting Data-level commentary is commonly used in real-life scenarios. PowerTable allows you to add comments to individual cells, rows, and columns in a table to provide context to your data, collaborate with your team by tagging them, and assign tasks. All of these features are available without the need for any coding. Here are the key highlights of the commentary solution provided by PowerTable: * Add comments and annotations to individual cells, rows, and columns. * Add a dedicated comment column to add row-level comments. * Tag users and stakeholders using @mention and reply to others' comments. * Tagged members are notified through an email instantly with the relevant link to take action at a data point level. * Assign tasks and collaborate with your team, especially for workflow use cases. * Using the commentary audit log, track tasks and audit the entire list of comments, including details like who wrote them, what they wrote, and when (date and time). * Comments are always associated with a specific data point, row, or column, so when the data is filtered, the comment appears at the appropriate location. The comments feature is located at the top of the toolbar, near your user profile. ## **1. Add New Comment** You can add formatted data-level comments, which are saved securely to the database, along with details such as author and time stamp. 1. Select a cell/column where you want to add a comment. Then click on **Comments.**

2. You can also click the **Add New Comment** option from the dropdown to add a new comment.

3. The comments editor opens up. Enter a comment and click on the highlighted icon.

4. The comment gets posted. You can see a green indicator on the top right corner of the cell. Hovering over it reveals the comment that has been added.

## **2. Edit a Comment** To edit a comment, hover over the cell containing the comment and click on the pencil icon. You can then start editing the comment.

## 3. Delete a Comment To delete a comment thread, click on the bin icon.

## **4.** Mention other users You can use the @ symbol to tag other stakeholders and draw their attention. 1. Click on a cell and then choose **Add a comment**. Type @ in the comment editor. You will see a list of the users in the workspace. 2. Select the desired user, add the comment, and click **Post.** 3. The comment gets posted successfully. ## 5. Reply to a Comment To reply to a comment, simply click on the comment and choose **Reply**. The comment reply gets added as a thread, and the number of replies is shown along with the comment. ## **6. Comments Column** You can add a dedicated comments column to capture row-level comments. 1. In the 'Comments' dropdown, turn the **Comments column** toggle on. The comments column gets added. 2. Click on any cell in the comments column. The comment editor opens. Enter a comment and click **Post**. 3. The comment gets added as shown in the below image. 4. To reply to a comment in the comments column, simply click on the comment and then select **Reply.** 5. The number of replies is also shown along with the comment. 6. To hide the comments column, turn off the toggle. ## **7. Hide All Comments** To hide comments temporarily, turn on the **Hide All Comments** toggle. ## 8. Email Notifications The ‘@user’ [mention](#id-4.-mention-other-users) in the comment automatically sends an email notification to the user with a link to the report so that the user can respond to this comment and update the data if required. Replies to the comments are also notified to the user instantly. 1. Email notifications on comment replies can be turned off. In the dropdown highlighted, click on **Profile Settings**. In the side panel, turn off the toggle. 2. Email notifications for new replies or mentions can be turned off. In the **Settings** panel, uncheck the highlighted checkbox. [This section](/explore-powertable/collaborative-commenting/comment-settings) covers all of the other options in the comments settings panel in detail. In the next section, we'll see how you can assign & track tasks. # Comment Settings Inforiver offers a variety of comment-related settings, including the ability to set up commentary access rules for users, lock/unlock threads, enable/disable notifications, and more. 1. Click on **Settings** in the 'Comments' dropdown. A side panel opens with a list of options as below. Let's take a detailed look at these options. ## 1. Enable Comments 1. By default, comments are enabled. To disable commenting, uncheck the highlighted checkbox and click **Save**. 2. You would no longer be able to add comments. ## 2. Show Comments Panel Check the highlighted checkbox to open the comments panel by default when you open the report. ## 3. Commentary Access By default, all users within the domain can add comments. However, if you want only specific users to be able to comment, select the highlighted option. A new field gets enabled where you can add the users. ## 4. Lock/Unlock Threads Locked threads cannot be edited, deleted, or replied to by any user. To do any of the above, the user must first unlock it. Using the option as shown below, you can allow only specific users to lock and unlock threads by specifying their email IDs. ## **5. Email notifications** To disable email notifications for new mentions or replies, uncheck the highlighted checkbox in the **Settings** panel. ## 6. Report Name You can modify the report name that appears in email notifications, as indicated below. ## 7. Delete all comments To delete all comments in the report permanently, click on **Delete all comments**. ## 8. Reset all settings Changes made in the settings panel can be reset using the **Reset all settings** option.

# View all comments & track changes Tracking the report changes using an interactive, filterable, and comment-enabled audit log ensures data governance. Our audit log captures date, time, user details, task status, and other information. There is a separate comments log that shows who commented on a specific data point and when. 1. To view all comments and tasks, click on the 'Comments' dropdown and choose **View All Comments.** 2. The side panel categorizes comments at the header and cell levels. You can see all the details here, such as the header name or cell ID where the comment is added, the names of the assignor and assignee, task status, thread replies, timestamp when they were added, and so on. 3. Click on the required cell ID or the header name to locate the comment. 4. **Filter comments:** Comments and tasks can be filtered by assignee, assignor, status, and time period. Choose 'Resolved' to view all resolved threads, 'My threads' to view discussions started by you, or 'All' to view all threads. Use the 'Assigned to' dropdown to filter by assignee. 5. **Filter by date:** By clicking on the highlighted 'Calendar' icon, you can filter comments and tasks based on a date range. 6. **Edit/delete comment:** To edit the comment, click the pencil icon, and to delete it, click the bin icon. 7. **Resolve/lock threads:** There are options for resolving or locking the thread, as shown below. A resolved thread signifies the completion of a task, while locked threads are closed to user edits and replies. 8. **Re-open resolved task:** You can re-open a resolved thread by replying to it or by clicking on the **Re-open thread** icon. 9. **Unlock thread:** To unlock a thread for editing or adding replies, click the icon shown below. 10. **Assign users:** You can also assign users from here. In the next section, we will look at the various comment settings. # Assign users & task status In this section, we’ll explore how to assign tasks to stakeholders and track their progress using the comments thread. Monitoring task status with terms like ‘Resolved', ‘Open', etc. ensures clear communication and efficient collaboration, especially when managing inputs from multiple stakeholders. You can achieve this with EDITable, which can help your team with accountability and improve workflow efficiency. ## 1. Assign tasks 1. Let's add a column-level comment and assign it as a task. Select a column and click on **Add a comment.** 2. Enter a comment. Click on the 'Assign a user' dropdown. 3. From the dropdown, select the desired user. 4. After selecting the user, click **Post** to post the comment. The status is set to 'Open'. 5. The task gets assigned. ## 2. Task status 1. By default, on assigning a task, it is set to 'Open'. To change the task status, click on the 'three dots' and select **Resolve thread**. 2. The thread is marked as resolved, as shown below. 3. Resolved tasks can be reopened if required by clicking on the 'Undo' icon. Alternatively, replying to any resolved thread will automatically make it open again. 4. Comments added in the comments column can also be assigned as tasks. Select a comment and assign it to a user. 5. By clicking on the arrow highlighted, you can expand/collapse the task status and assignee columns. Based on the selection in the comments editor, these columns get populated automatically. 6. The status and assignee can be viewed in the columns as below. These columns are available when you enable the comments column and add comments to it. In the next section, we'll cover how you can track task status and changes for all comments. # Formatting & Display This section describes the formatting and display options available in PowerTable. You can use the settings available here to customize the appearance of your table app. # Basic formatting The toolbar contains a host of formatting options, as shown below:

1. **Undo/Redo:** Use these options to quickly undo or redo recent changes to data or formatting, allowing for easy corrections. 2. **Row Height:** Set the desired row height for the table. 3. **Word Wrap:** This option wraps long text to multiple lines for improved readability. 4. **Auto Fit:** Adjust column widths with options such as "Fit to Header," "Fit to Content," or manually setting the column width to your preference. By selecting a row or a column, you'll get cell and text formatting options such as, **Bold, Italic, underline, fill color, font color, and border style.** When you select a column, along with these options, you have options to align the column values to the left or to the right.

# Display Settings Customize the visual appearance properties of your table to match your desired style and format. Click on the **Appearance** tab.

## 1. Font This option lets you choose your preferred font style for the table. ## 2. Font Size Using this option you can adjust the font size in the table. ## **3. Text Color** You can change the color of the text/values in the visual using this option. ## **4. Header Orientation** This option lets you set the orientation of the header text: horizontal, vertical, clockwise, and counterclockwise. ## **5. Header Text Color** You can change the color of the header text in the visual using this option. ## 6. Display Type in Header Toggling on this option displays the input type of the respective columns along with headers. ## 7. Row Highlight This option allows you to highlight the rows in the report. The various row highlight types are: * **Row Hover** * **Row Alternate** * **Row Alternate Contrast** * **Row Alternate Flashy** * **Off** ### **7.1. Row hover** Highlights the row upon hovering over them. By default, all the tables are configured with the 'Row Hover' highlight option. The following options are displayed in this type: **Hover Text** - This option allows you to set the text color of the row on hover. **Background** - This option allows you to set the background color of the row on hover. ### **7.2. Row alternate** This option highlights alternate rows. All of the row highlight options are customized with predefined colors. If you enable this option, the following options are displayed: **Odd row** - This option allows you to set the color of the odd rows. **Even row** - This option allows you to set the color of the even rows. ### **7.3. Row alternate contrast** This option highlights the odd and even rows with contrasting color shades by default. If you enable this option, the **Odd row** and **Even row** options are displayed. ### **7.4. Row alternate flashy** This option highlights the odd and even rows with flashy color shades. If you enable this option, the **Odd row** and **Even row** options are displayed. You can remove the row highlight by selecting the **Off** option from the drop-down. ## 8. Table Padding This option allows you to insert spacing/padding for the table from the top, bottom, left, and right. The minimum and maximum padding values are 0 and 100 respectively. ## 9. Gridlines You can add **Horizontal**, **Header**, and **Vertical** properties for gridlines. ### **9.1. Horizontal** This option lets you set the horizontal gridlines for the rows. You can select from the following options: * **Solid lines** * **Dashed lines** * **Off** If the 'Solid Lines' or 'Dashed Lines' option is selected for the horizontal gridlines, then a color picker is enabled. Using the color picker, you can customize the color of the single/double horizontal gridlines. You can also set the number of rows to skip between 2 consecutive lines. ### **9.2. Vertical** This option lets you insert vertical lines between columns. You can select from the following options: * **Solid -** This option will insert solid vertical lines to the right of each column. * **Off** - This option will turn off the vertical lines If the 'Solid lines' option is selected for the vertical lines, a color picker option is enabled. Using the color picker, you can customize the color of the solid vertical lines. ### **9.3. Header Border** This option lets you insert gridlines for the column headers. If this option is enabled, then a color picker option becomes available next to it. Using the color picker option you can customize the color of the header gridline. The color of the header gridline is the same as that of the horizontal gridlines by default. If you change the color of the header gridline, the color of the horizontal gridlines also gets changed accordingly. ### **9.4. Skip lines** This option allows you to set the gridline gap between the rows. The minimum and maximum skip line values are 1 and 10 respectively. ## 10. Show Outline This option allows you to set the outline for the visual. If you enable this option, a color picker is enabled. The color picker allows you to select the color of the outline. # Formulas & Functions PowerTable features an Excel-like formula engine that supports over 50 functions, including logical, Boolean, date, math, and others. The formula editor is intuitive and user-friendly and offers dynamic help suggestions regarding syntax and examples. It includes features like auto-complete and multi-line support to better assist users in creating and troubleshooting formulas. Formulas are used while inserting a formula column or to set up data-derived default values. A formula can contain functions, expressions, references, and constants. 1. A function takes in one or more values and performs a defined operation to return a result. Every function is defined with a keyword and a specific syntax that is to be followed. 2. Expressions are references with logical or mathematical operators that specify the operation to be performed. 3. References are the cell names containing values. 4. Constant refers to a value or a number that does not change. ## Formula Column {% hint style="info" %} Please refer to [this section](/explore-powertable/working-with-powertable/column-setup/add-formula-column) to understand the steps to insert a formula column. {% endhint %} While adding a formula column to your table, you can just start typing in the required formulas in the formula box. As you start typing, a help menu appears, allowing you to autocomplete your formulas. To get suggestions about formulas or insert them, click the **Functions** tab. The **Reference** tab allows you to add columns and refer to them in your formula.

## Default Value Default values automatically populate columns with predefined data when none is provided, helping maintain data consistency, prevent nulls, and simplify data entry. You can manually enter static data or set up a formula for a dynamic default value, whose value is derived and thus dependent on existing data.

PowerTable formulas can be classified into: * [Conditional Statements](/explore-powertable/formulas-and-functions/conditional-statements) * [Logical Functions](/explore-powertable/formulas-and-functions/logical-functions) * [Math Functions](/explore-powertable/formulas-and-functions/math-functions) * [Text Formatting Functions](/explore-powertable/formulas-and-functions/text-formatting-functions) * Date functions * Other Operators # Conditional Statements

NAME	DESCRIPTION
IF	Checks a condition and returns one value when it is TRUE; otherwise, it returns a second value.
IFNA	Evaluates an expression and returns a specified value if the expression returns an #N/A error; otherwise, it returns the value of the expression itself.
SWITCH	Evaluates an expression against a list of values and returns one of multiple possible result expressions. The first matching value is returned. If there is no match, an optional default value is returned. This function can be used to avoid having multiple nested IF statements.
FILTERIF	Returns the list of items that match the given condition.

# IF The IF function checks a condition and returns one value when it is TRUE; otherwise, it returns a second value. ### Syntax ```dax IF(logical_test,value_if_true,value_if_false) ``` ### Arguments logical\_test – Any value or expression that can be evaluated as TRUE or FALSE. value\_if\_true – The value to be returned if the logical test is TRUE. value\_if\_false – The value to be returned if the logical test is FALSE. ### Return value The return value can be either *value\_if\_true* or *value\_if\_false*. ### Example 1 ```javascript IF(PY > 0,(AC-PY)/PY,0) ``` The above formula returns (AC-PY)/PY if the PY value is greater than 0; otherwise, it returns 0. ### Excel equivalent [IF](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2) # IFNA The IFNA function evaluates an expression and returns a specified value if the expression returns an #N/A error; otherwise, it returns the value of the expression itself. ### Syntax ```javascript IFNA(value,value_if_error) ``` ### Arguments value- The argument that is checked for the #N/A error value. value2- The value to return if the formula evaluates to the #N/A error value. ### **Return value** Either *value1* or *value2.* ### **Example** ```javascript IFNA((AC-PY)/PY,0) ``` If the value is a number, the function returns (AC-PY)/PY; otherwise, it returns 0. ### **Excel equivalent** [IFNA](https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-42fc-a49d-79b4951fd461) # SWITCH Evaluates an expression against a list of values and returns one of multiple possible result expressions. The first matching value is returned. If there is no match, an optional default value is returned. This function can be used to avoid having multiple nested [IF ](/explore-powertable/formulas-and-functions/conditional-statements/if)statements. ### Syntax ```javascript SWITCH(expression,val1,res1,val2,res2,...,val_n,res_n,default) ``` ### Arguments expression – The expression or statement to be evaluated. val1, val2, …, val\_n – The list of values to be compared against the expression. res1, res2, …, res\_n – The result to be returned in case of a match. default (Optional) – The default value to be returned if the expression does not match with any of the values in the argument list. This is an optional argument. ### **Return value** The relevant result is returned when the expression matches the value; otherwise, a default result is provided if there is no match. ### **Example** ```javascript SWITCH([AGE],30,"Young",55,"Old","Other") ``` Will return "Young" if the age is 30, "Old" if the age is 55, and "Other" for all other ages. ### **Excel equivalent** [SWITCH](https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e) # FILTERIF The FILTERIF function can be used to filter an array of values based on a given condition. It returns an array—hence, it has to be used in conjunction with an aggregate function like SUM or AVERAGE. It can be used to populate a calculated row. ### Syntax ```javascript FILTERIF(list,condition) ``` ### Arguments list – The input list of values on which the filter is to be applied. condition – The condition to be evaluated. ### Return value The function returns an array. ### Example 1 ```javascript FILTERIF([AGE],">50") ``` The formula above will use FILTERIF to filter all the rows of people whose age exceeds 50. ### Example 2 ```javascript AVERAGE(FILTERIF([Column1,Column2,Column3],">1000")) ``` The above example calculates the average of values in Columns 1, 2, and 3 that are greater than 1,000. # Logical functions

NAME	DESCRIPTION	SYNTAX	EXPLANATION	EXAMPLE
AND	Returns TRUE only if all the conditions are TRUE; otherwise, returns FALSE.
IN	Returns TRUE if the list of values being searched is in the array
ISBLANK	Returns the logical value TRUE if the value argument is a reference to an empty cell.	ISBLANK(value)	Returns TRUE if AC is empty	ISBLANK(AC)
ISEMPTY	Returns the logical value TRUE if the value argument is a reference to an empty cell.	ISEMPTY(value)	Returns TRUE if AC has no value	ISEMPTY(AC)
ISNUMBER	Returns the logical value TRUE if the value argument is a valid number	ISNUMBER(value)	Returns AC-PY/PY if the value is a number else returns 0	IF(ISNUMBER((AC-PY)/PY), (AC-PY)/PY , 0)
NOT	Changes FALSE to TRUE and TRUE to FALSE.	NOT( logical_test )	Will return 50 if the region is not West otherwise returns 25	IF(NOT(Region == "West"), 50, 25)
OR	Returns TRUE if any of the conditions are TRUE and FALSE only if all arguments are FALSE.	OR( logical_test1, [logical_test2], ... )	Will return 25 if the region is West or East otherwise returns 30	IF(OR(Region == "West", Region =="East"), 25, 30)
XOR	Returns a logical 'Exclusive OR' of all the arguments	XOR( logical_test1, [logical_test2], ... )	Will return 25 for all category in the West except for category Urban, and returns 25 for all Urban category under all region except for west, for all other items returns 30	IF(XOR(Region == "West", Category=="Urban"), 25, 30)

### Logical Operators

TITLE	OPERATORS	DESCRIPTION
AND	&&	Logical AND
OR	\|\|	Logical OR

# AND The AND function returns TRUE if all the conditions are TRUE and returns FALSE if any of the conditions are FALSE. ### Syntax ```javascript AND(logical_test1,logical_test2, ... ) ``` ### Arguments logical\_test1 – The first condition to be evaluated. This is a required argument. logical\_test2,.. – The additional conditions to be evaluated. These are optional arguments. ### **Return value** Either True or False ### **Example** ```javascript if(and(Region == "West", Category=="Urban"), 25, 30) ``` Returns 25 if the region is West and the category is Urban, otherwise returns 30 ### **Excel equivalent** [AND](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9) # Math functions

NAME	DESCRIPTION	SYNTAX	EXAMPLE	EXPLANATION
ABS	Returns the absolute value of a number, a number without its sign	ABS(value)	ABS(COLUMN1)	Returns the absolute value of COLUMN1
AVERAGE	Returns the average of the arguments	Average(value1,[value2]....)	AVERAGE(COLUMN1, COLUMN2)	Returns the average of COLUMN1, COLUMN1
AVERAGEIF	Returns the average of the filtered values that satisfy the given condition	AVERAGEIF([Range], condition)	AVERAGEIF([Quantity],[Units Sold], “>10000”)	Returns the average of Quantity and Units Sold if they are above 10000
AVERAGEEXNEG	Returns the average of the arguments excluding negatives	AverageExNeg(value1,[value2]....)
AVERAGEEXZERO	Returns the average of the arguments excluding zeros	AverageExZero(value1,[value2]....)
AVERAGEEXZERONEG	Returns the average of the arguments excluding zeros and negatives	AverageExZeroNeg(value1,[value2]....)
CEILING	Rounds a number to the nearest integer or to the nearest multiple of significance	CEILING(value, significance)	CEILING(COLUMN1)	Returns the ceiling of COLUMN1 to the significance of two decimal
COUNT	Counts the number of items in the list	COUNT (value1,value2....)	COUNT(Column1,Column2,Column3)	Returns the count of the number of items in a range
COUNTIF	Returns the count of items that match the specified condition	Count(list, condition)	COUNTIF([100,500,120],"<200") COUNTIF([[Quantity],[Units Sold]],BLANK)	Returns 2, since only two value in the given list matches the condition Returns 1,2 or 0 depending on how many blank values are in Quantity, Units Sold in each row. This value can be used to set conditional formatting or used with IF condition to fill value in another column
DIVIDE	Returns the division of two values	DIVIDE(Numerator, Denominator, Alternate)	DIVIDE(COLUMN1, COLUMN2, 0)	Returns COLUMN1/COLUMN2 and if any error, returns 0
EVEN	Rounds to the nearest higher even number
EXP	Returns e raised to the power of a given number	EXP(value)	EXP(COLUMN1)	Returns the exponential of COLUMN1
FLOOR	Rounds a number (towards zero) to the nearest specified multiple of significance	FLOOR(value, significance)	FLOOR(COLUMN1)	Returns the floor of COLUMN1 to the significance of two decimal
INDEXOF	Returns the index of the first occurrence of a number in an array of numbers
LOG	Returns the logarithm of a number to the base specified	LOG(value,base)	LOG(COLUMN1, 10)	Returns the Log to the base 10 of COLUMN1
MAX	Returns the largest number in a set of values	Max(value1,[value2]....)	MAX(COLUMN1,COLUMN2,COLUMN3)	Returns the maximum of COLUMN1, COLUMN2, COLUMN3
MIN	Returns the smallest number in a set of values	Min(value1,[value2]....)	MIN(COLUMN1,COLUMN2,COLUMN3)	Returns the minimum of COLUMN1, COLUMN2, COLUMN3
MROUND	The MROUND function rounds a number to the nearest multiple of a specified value.
ODD	Rounds to the nearest higher odd number
POWER	Returns the result of a number raised to a power	POW(value,power)	POW(COLUMN1, 2)	Returns the COLUMN1 to the power of 2
RAND	Returns a random number between 0 and 1	RAND()	RAND(0,1)	Returns a random number like 0.1,0.2,..
RANDBETWEEN	Returns a random number between two values	RANDBETWEEN(value1,value2)	RANDBETWEEN(0,100)	Returns a random number between 0 and 100
ROUND	Rounds a number to the specified number of decimal places	ROUND(value, significance)	ROUND(COLUMN1, 2)	Returns the round of COLUMN1 to the significance of two decimal
ROUNDDOWN	The ROUNDDOWN function always rounds a number down, toward zero
ROUNDUP	The ROUNDUP function always rounds a number up, away from zero
SQRT	Returns the square root of a number	SQRT(value)	SQRT(COLUMN1)	Returns the exponential of COLUMN1
SUM	Returns the sum of all the numbers in the argument	Sum(value1,[value2]....)	SUM(COLUMN1, COLUMN2)	Returns the sum of COLUMN1 and COLUMN2
PCT	Used for a percentage calculation	PCT(value:number)	SALES + PCT(10) = SALES + 10%	Returns percentage value

### Finance Function


PMT	Calculates the payment for a loan based on constant payments and a constant interest rate

# Text formatting functions

NAME	DESCRIPTION	SYNTAX	EXAMPLE	EXPLANATION
CONCATENATE	Concatenates two or more text strings into one string	CONCATENATE(string1, string2, ..)	CONCATENATE("Today's price is ",TEXT(6000,"$###,###.00"))	Will return "Today's price is $6,000
HYPERLINK	Returns hyperlinked text which will navigate to a given URL
LEFT	Extracts text from the left of the value up to the number of characters specified	LEFT(text/number, [num_chars])	LEFT([Full Name],4)	When field Full Name has values such as Johny Rivers, this will return John
LOWER	Converts a text string into lowercase
MID	Extracts text from the mid of a string from the position provided up to the number of characters specified in length	Mid (text, position, length)	MID([Full Name],0,7)	When field Full Name has values such as Johny Rivers, this will return Johny R
PROPER	Converts the first character of a text string into uppercase
REPLACE	Replaces an input text string with a given value
REPT	Repeats a given text string a specified number of times
RIGHT	Extracts text from the right of the value up to the number of characters specified	RIGHT(text/number, [num_chars])	RIGHT([Full Name],6)	When field Full Name has values such as Johny Rivers, this will return Rivers
TEXT	Formats the value to the given format string	TEXT(value, format)	TEXT(6000,"$###,###.00")	Will return $6,000
TRIM	Removes leading and trailing spaces
UPPER	Converts a text string into uppercase
VALUE	Converts a text string that represents a number to a number	VALUE(value:string)	VALUE(MID”ABC-123-WEW”,5,3))	Returns “123” as number

# Date functions

NAME	DESCRIPTION
DATEADD	Adds a specified number of days to the input date
DATEDIFF	Returns the number of days between two input dates
DAY	Extracts the day of the month from an input date
DATE.SET	Returns a date after changing the format
EOMONTH	Returns a date specifying the last day of the month
FROMEXCELDATE	Converts an input date to a excel format date serial number
MONTH	Extracts the month from an input date
NOW	Returns the current date and timestamp
PERIOD_RANGE	Returns an array of date ranges between two specified dates
SHOWDATETIME	Returns the date and time period based on a given set of arguments
TIMEZONE	Enables you to refer to a specific UTC time zone.
TODAY	Returns the current date
TOEXCELDATE	Converts an excel recognized serial number to the equivalent date
YEAR	Extracts the year from an input date

# Other operators | OPERATORS | DESCRIPTION | | --------- | --------------------------------------- | | +,- | Addition & Subtraction | | >, >= | Greater than & Greater than or equal to | | =, != | Equal to & Not equal to | | \*, / | Multiplication & Division | | () | Parenthesis | | +, - | Plus & Minus(-1) | | <, <= | Less than & Less than or equal to | The mentioned arithmetic and logical operators can be used with cell references and column measures as operands. These operators can also be used with cells/columns containing calendar dates. # PowerTable Console The PowerTable console, along with the [admin portal](/explore-powertable/admin-portal), provides an intuitive, sleek, and user-friendly interface for creating and managing tables, viewing and exporting transaction logs, monitoring the metrics, managing users, and more. You can access it by clicking on **Lumel Admin Console.**

1. The **PowerTable console** allows you to view and manage source tables, transaction logs, and metrics, as well as manage your profile and onboarding actions.

2. The **Admin Portal** provides exclusive configuration options for workspace admins, such as user management and other important settings, including general settings and writeback settings.

The PowerTable console has the following menus and functions: **Tables** - The 'Tables' menu displays a list of all tables configured in the PowerTable visual. Selecting any table on the left pane displays its configuration and connection details, as well as the transaction logs and reports on the right. **Transaction Logs** - This option displays all types of transactions made on all the configured source tables by all users. **Insights/Metrics** - This option lets you view the ongoing, completed, queued, and failed jobs. You can also monitor the general health of the queueing system, email services, external applications' availability, and cloud storage connectivity. **Manage Profile** - The 'My Profile' section in the PowerTable console allows you to view and manage your profile, integrations, writeback connections, and license details. **Onboarding Actions** - This option opens a pop-up window that contains a list of onboarding actions like user management, application permissions, consents, and configurations of database connections for writeback/sync. **User Management** - This option available within the admin portal allows you to manage users and assign their roles. **Settings** - This option lets the admin configure the general and writeback-related settings. # Tables This section displays a list of tables configured in the PowerTable visual on the left pane and their corresponding configuration details on the right side when clicked on each one of them.

Use the search box to quickly search for the required table. Upon selecting a specific table, the user can view three main items corresponding to that table: general details, approvals, and transaction logs. ### General This page shows the column and connection details of the table. * **Columns**: This section contains information about the columns in the table, such as column names, their data type, default values if they are configured, null/not-null property, any set constraints, relation, etc. * **Connection Details:** The following connection details can be seen from this section: * **Connection Type**: The type of connection (e.g., Fabric Warehouse, Azure SQL, Snowflake, BigQuery, etc.). * **Account**: Account information associated with the table. * **Username**: The user who created or manages the table. * **Created At**: Timestamp of when the table was created. * **Updated At**: Timestamp of the most recent update to the table. * **Updated By**: The user who last updated the table. * **Reports**: This part lists all published reports that use this connection and table, with their URL. ### Approvals This page lists all approvals related to transactions involving the particular table. Here, you can track each approval's status and complete details of the request, such as the ID, requested user, approvers, created time, and reviewed time. **Search:** You can search for specific approval requests using the search option. **Filter:** You also have the option to filter approvals based on the requested user, status, approver, requested time, and reviewed time. ### Transaction logs This tab logs all transactions made to the selected table and provides a historical record of changes for auditing and tracking purposes. You can view the complete details of every transaction, including the job ID, transaction type, number of rows affected, duration, status, the user’s name, and the time it was completed. 1. Search for a transaction using the search option. 2. Filter transactions using the ID, transaction type, user's name, status, start time, and the job type. 3. Click on any transaction ID to view detailed information about the transaction. --- [Next Page](/llms-full.txt/1)