Introduction to Table Editor v26.1

Power ON's Table Editor (TE) is a custom visual developed for Microsoft Power BI. It enhances the user experience by enabling end-users to make permanent changes to data (write-back), enriched with text input controls commonly seen on modern HTML pages (example, date pickers, dropdowns, rich text) alongside the traditional look and feel.

The Table Editor product documentation provides a detailed overview of the visual and how to use it in reports. It covers common use-case implementations, proper configuration, and strategies for overcoming potential pitfalls. This documentation is intended for both technicians (developers, DBAs, BI professionals) familiar with SQL Server, SSAS Tabular models, Azure Services, Excel, and Power BI, as well as users who primarily focus on building and preparing reports using Excel or Power BI Desktop. Some sections focus on more technical subjects.

Table Editor allows users to edit any type of row-level information in Power BI. The most typical use case is managing dimension-type data (such as customers, products, etc.) - adding or removing records, editing existing members, changing attributes, as well as performing simple data entry tasks or adding comments to existing fact table records. This visual can be considered a replacement for a simple form-like application designed for master data or dimension data management.

Table Editor offers the following built-in features and components:

• Writing back values to the underlying data source
• Date Time picker for DATETIME data type columns
• Check Box control for BIT data type columns
• Dropdown list (called Selection) control for replacing keys with values (example, selecting Customer Name instead of Customer ID while saving the key back to the underlying table)
• Search box that acts like an auto-complete field for frequently used values
• Multi-line text box
• Supports copy and paste of selected cells to/from VTE
• Paging and sorting
• Conditional formatting for numeric type columns
• Rich Text display for Text Area type columns
• In the case of an Excel Export (built-in Power BI feature), datasets can be exported up to 150,000 rows, not just the first 30,000

Following is the high-level overview of how write-back works for Tabular models. Depending on your data source (SSAS In-Memory, SSAS Direct Query, or SQL only) Power ON’s write-back service performs the following steps:

1. The service captures the modified value along with its tuple (the intersection of dimensions used in the calculation of measure for the given cell) and the user context. Based on the SSAS model structure (relationships, table queries, and measure definitions) the service composes T-SQL statements for execution.
2. It executes the compiled T-SQL statement against the underlying data source (fact table) to save the modifications.
3. For SSAS in-memory models, the service reprocesses the table. See Performance Optimization for more information.
4. It refreshes the visual to display the changes in the report.

Important: Note that the target table for the write-back must have a primary key defined, with the following considerations:

• Composite keys are not supported.
• For SSAS data sources, the primary key must be part of the SSAS model, visible to end users, and added to the visual as a field.
• If there are specific business rules for creating a key for a new record, you must either create a custom trigger in your database to provide the new value during the operation, or if the key can be computed based on existing data visible in the model using DAX, the Computed Column property can be utilized (see Configuration Options > Column Properties for more information).