Introduction to Power XL Table v26.1

Power ON’s Power XL Table (PXLT) 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 Power XL Table 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.

Power XL Table allows users to edit any type of row-level information in Power BI. The most typical use case is Master Data Management (example, customers, products, materials), including adding or removing records, editing existing members, changing attributes, and performing simple data entry tasks. Users can consider this visual as a replacement for a simple form-like application designed for master data.

Power XL Table offers the following built-in features and components:

• Writing-back values to the underlying data source
• Date Time, Month, or Time picker for DATETIME data type columns
• Color picker for color-like data type columns
• Check Box or Radio button control for true/false data type columns
• Dropdown list (called Selection) control
• Supports copy and paste of selected cells to or from Power XL or Microsoft Excel
• Conditional formatting for any type of columns
• Setting tens of predefined table formats, cell styles, chart options, sparklines, and more

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: The target table for the write-back must have a primary key defined. Note the following:

• Composite keys are supported.
• For Semantic data sources, the primary key must be part of the Semantic 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.