Skip to main content

Power XL Table Usage

This topic outlines how to use the Power XL Table visual to create comprehensive reports.

Power XL Tab

Layout Operations

Save Layout

The Save Layout button is used to save the configurations and settings made on the designer ribbon or within the sheets. When the save layout process begins, a loading circle in the top right corner indicates that the system is actively saving the layout. Once the process is complete, a Layout Saved message appears, confirming that the configurations and settings have been successfully saved.

Important: The Save Layout functionality focuses on preserving layout preferences and settings without directly impacting the database. This feature allows users to save and recall their customized layouts conveniently, without affecting the underlying data.

If you have a complex PXLT report with calculations that refer to different columns and plan to add more columns to the table, please consider the following recommendations:

  • Save the layout after adding new fields: Whenever you add a new field to your report, ensure you save the layout. When the layout order changes, a formula recovery code runs automatically in the background. If you make further changes without saving the layout, those unsaved changes will be ignored, treating all modifications as simultaneous and complicating code management. To avoid this, always save the layout after adding a new field or changing the column order.
  • Use actual column names: It is recommended to use actual column names, for example, @ColorName instead of references based on column letters, such as 'B6' or 'AD6'. This practice helps maintain clarity and reduces errors in your calculations.

    For example, use this instead of this .

Import Layout

This button allows you to import an Excel file that may contain multiple sheets with tables and data. If a new file is imported over an existing one, the new import will overwrite the previous data. If Power XL Table does not have a data table to merge with the Excel data table, the following error message appears: Importing data underneath the data sheet is not supported.

Reset Layout

Click the Reset Layout button to delete the current layout, clear the content of external cells, and cancel any ongoing imports. It does not reset Power BI settings on the designer ribbon. A confirmation message appears to verify the reset action once done.

Main Configuration

Writeback Settings

  • Save Confirmation Pop-up - Set the time interval (in seconds) for displaying save confirmation pop-ups. A default value of 0 keeps the pop-up on screen until manually closed. You can configure up to 30 seconds for the pop-ups to close automatically.
  • Windows Authentication - In an on-premises environment using Power BI Report Server, enable this setting to properly pass Windows credentials to the underlying data source. If the reports are published to the Power BI service using Azure AD, disable this setting to utilize AD credentials when accessing the data source.

    Enabling Windows Authentication in Gateway will cause the visual to post the Windows login context instead of using Power BI service credentials within the write-back service request. This value (for example, domain\user instead of username@domain.com) will be set when using USERNAME() in computed and/or default value columns, as well as in SQL context variables. Additionally, this enables impersonation and must be enabled if Windows Authentication is required for authentication in IIS for the write-back service.

  • Auto Refresh - When enabled, the visual refreshes automatically after saving. When disabled, it does not refresh after the Save Changes action, allowing you to view the changes. This setting is enabled by default.
  • Empty Cell Writeback Behavior - Allows you to control how empty cells are handled during writeback operations. Select one of the following options:
    • Write As Empty String (Default) – Sends "" to represent empty cells, which is the standard behavior for most systems.
    • Write As Null – Sends NULL to represent empty cells for systems that explicitly require null values during writeback.

    • Refer to Power XL Table Setup and Configuration to review the other settings in this panel. 

Column Settings

To access these settings, click the Column Settings button on the Power XL tab. There are two tabs in the Column Settings panel - Basic Settings and Write Security.

Column Type

To access this setting, click the Column Settings button on the Power XL tab and select the dropdown menu in the highlighted area.

The data types for the columns on the visual do not need to be set because they are automatically recognized by Power BI. However, changes made in this setting will be influenced by how the cell is rendered and how it behaves.

Types can only be set for columns for which aggregation is set to Don’t summarize in the report field’s list window, as only these fields can be saved during write-back. For columns which are not part of the Power BI dataset (local columns, created in the Power XL Table visual), this setting remains hidden.

The following types can be picked:

Text

One-line Textbox in the grid.

Number

This type should be used in all numeric data types: decimal, currency, and integers.

Selection

The values in the drop-down are sourced from a related or lookup table, requiring additional properties to be configured for this column type. For instance, when a data table has a foreign key with a numeric column that is in a relationship with another table, you can display the corresponding text representations instead of numeric values and allow selection from a set of values.

Configure the following properties:

  • Source Table - The drop-down list values are sourced from the source table. Enter the table name as displayed in the Fields pane. For SQL databases, provide the SQL table name. If the table is in a different schema than "dbo", use the format: schemaName.TableName.

    • When populating this field, the visual can access the data in two ways. By default, it uses the write-back service to query the underlying data source. However, this method does not honor RLS defined in SSAS, as all data is fetched from the source SQL table. Additionally, this extra round trip to the server can sometimes be slow. To improve performance and honor existing RLS, you can use the following technique:

    • Import the SmartFilter Helper visual into the report and add it to the report page. You can use the one provided in the setup kit or download it from the Power ON Store (store.poweronbi.com).
    • Add the fields used for the drop-down to the SmartFilter visual (Display Column (name), Value Column (ID), Parent Value).

      • This method ensures that the available values for the dropdown are stored in the SmartFilter, allowing Power XL Table to recognize them and preventing it from querying the write-back service.

  • Display Column - Specify the display name for the column. This is the text field from the lookup table that is displayed after the lookup.
  • Value Column - Specify the value ID behind the display name. This is the key column in the lookup table that corresponds to the value in the current column.
  • Parent Column Name - Specify the parent column name to enable cascading validation for the selected column. This child column will present options in the drop-down based on the parent column. Use the column name as per the data table, not the field name.
  • Parent Value - Specify the parent column value as in the SmartFilter. This is an optional field; if the Parent Column Name value matches the field name in the SmartFilter, the system searches for that value. For cascading validation, the SmartFilter can handle multiple fields.

Cascading Validation

Cascading drop-downs ensure that subsequent drop-down lists display options based on the selection made in the preceding drop-down. For example, selecting Europe in the Continent drop-down will filter the Country drop-down to only show countries in Europe. If you then select Hungary as the country, the City drop-down will display cities in Hungary. The Parent Column Name and Parent Value fields enable this feature. Cascading validation also works with other column types, but all levels must have the same type.

Cascading drop-downs work with both hierarchical and flat tables:

  • Hierarchical Data Table - Data tables are arranged hierarchically, where the lower-level table references the table above it, such as the Country table referencing the Continent table. For hierarchical data structures, you need multiple SmartFilters, one for each data table. When using a SmartFilter as the source table, the Source Table field must be configured with the filter associated with that column, for example, continent for continent, country for country, and so on. In the SmartFilter, users can combine fields from different hierarchies.

  • Flat Data Table - This is a consolidated table containing all the data in a single table. With a flat table, you can use either multiple or a single SmartFilter to retrieve the full data table. When using a single SmartFilter as the source table, the Source Table field must be configured with the single filter, for example, Location for the continent, country, and city columns.

Recommendations for using cascading validation:

  • Use a single SmartFilter to retrieve values from a flat data table.
  • Use a single SmartFilter to retrieve values from a hierarchical data table, unless the fields are not connected.

Limitations of cascading validation:

  • Currently, it supports up to 30,000 records in the drop-down list. If you select a value near the end of the list, subsequent child drop-downs may not display all values due to exceeding the 30,000 record limit in larger datasets.

 

Tip: If the drop-down list is lengthy and not all values are visible, a scroll bar will appear on the right side. Additionally, you can search among the values by typing the first letter, which will jump to and highlight the first value starting with that letter. For example, typing "h" will automatically jump to and highlight the "Headphones" value.

Note: You can restrict the available values in the drop-down list. After adding the fields to the SmartFilter in the report, apply a visual-level filter on the SmartFilter to limit the available values. Consequently, not all possible values will be visible in the drop-down for selection purposes; however, rows in the Power XL Table visual with values outside the available ones will still be visible.

Date

A data picker is brought up by the Date type where the date can be set.

The SQL Database field should be of the type Date or DateTime.

Custom

Modifying the cell type, validation, or settings of an existing column will result in setting the column type to custom.

This type of column can be deleted by clicking the Reset button.

Checkbox

Column types for Boolean values can be set to Checkbox.

Rich Text

The content of this type of column can be edited in the Custom Rich Text Editor in both Edit or Reading View mode. The value is displayed in the same way as in TEv2 rich text type.

The editor opens instantly when they are double-clicked or the cell is typed into, AND the content is not(!) read-only.

The editor does not open if the column / cell is read-only or if a write security rule makes the column / cell read-only.

The rich text column content can be saved with the blue floppy icon inside the editor.

Power XL Table can display the rich text type columns created in Table Editor, and vice versa.

Auto resize row height

When the content is changed in a rich text column type cell; the row will resize to show the whole content of the cell. The row height follows the size of the content.

When the visual is loaded, the rows will be displayed according to the content, and the column width will not change.

Visibility of Rich Text Editor option in context menu

The context menu appears when the cell is right-clicked.

The Rich Text Editor option in the context menu is only visible when the selected cell is outside of the data table. In this case, the original Rich Text Editor opens, not the custom Rich Text Editor.

Key Column

To access this setting, click the Column Settings button on the Power XL tab.

Select the column or columns that represent the primary key of the table. Power XL Table requires a unique value per row to be defined as the primary key, which might be an identity column from SQL Server. If the key value is not automatically generated when inserting new records, you must enter a value manually or use DAX calculations to define a default value. Composite primary keys are also supported.

Row Filter Column

To access this setting, click the Column Settings button on the Power XL tab.

By default, row selection filtering uses the key column defined in the Key Column setting. To filter by a different column, turn on this setting for that column.

Required

To access this setting, click the Column Settings button on the Power XL tab.

Turning this setting ON makes the column mandatory. If the required field has a blank value, changes will not be saved.

Read Only

To access this setting, click the Column Settings button on the Power XL tab.

Turning this setting ON prohibits any kind of update on the given column.

After it's turned ON for a column, it will become read-only when the report is saved and exits Edit mode.

If a column originates from a different table than the source, it will be automatically set to read-only.

Exclude From Save

To access this setting, click the Column Settings button on the Power XL tab.

If enabled, the value will not be retained during the write-back process. The Read Only setting should be enabled when Exclude From Save is selected.

Columns originating from a different table than the source, or those that are aggregated, will be automatically set to be excluded from the save.

Write Security

To access this setting, click the Column Settings button on the Power XL tab and then select the Write Security tab.

Modifications on a row can be restricted by creating a calculated column or measure in a model or report. The complexity of the formula is unrestricted, allowing any logical evaluation of the existing columns and filter context to determine if the selected row is editable.

One of the following values must be returned by the write security column:

  • To disable modifications, it needs to be returned: 0, "0", null, "null", "NULL", false, "false", "FALSE", or ""
  • To allow modifications, any other value needs to be returned, preferably 1 or TRUE.

For example, the following measures can be utilized:

  • CanWrite = IF(VALUES('Product'[BrandId]) = 1, "", 1)
  • The above measure makes products belonging to the Brand with ID 1 read-only.

For more complex cases, where security control is based on user privileges, the following pattern can be used:

  • IsWriteable = MAXX('UserMapping', IF('UserMapping'[UserName] = USERNAME(), "", 1))
  • This measure must be added to the Power XL Table, and the write security property for this column must be enabled.

For a detailed example, please refer to the following article:

https://help.insightsoftware.com/s/article/implement-complex-write-security-per-entity

The Apply to Selected Columns checkbox becomes available only when the Write Security setting is enabled. It allows you to specify which columns should be subject to write security measures. If this checkbox is disabled, write security applies to all columns. This setting is also available under Column Properties for each individual column. Enabling it marks the column as a Protected Column.

  • Available Columns - Lists all the available columns in the grid.
  • Protected Columns - Move the required available columns to this list to make them non-editable and ensure all cells within these columns are read-only. Select the required column or click Select All and then click Add to Protected.

A column will now be fully editable only when the visual is in Edit mode and no write security measures are applied to it, which means the write security column must not return a value of 0 or null.

Security and Validation

Enable Add

To access this setting, click the Security and Validation button on the Power XL tab.

The Enable Add feature allows adding a new row to the data table. After the new row is added, new data can be added to populate the fields within. If the checkbox is not selected, a new row cannot be inserted into the data table using any method in Reading View.

However, it's essential to note that pressing the buttons alone does not automatically save the changes. To finalize the action and ensure that the changes are saved, users must click the Save Changes button. This step guarantees that any modifications made, including the insertion or deletion of rows, are committed. It ensures that changes are permanently saved.

Enable Delete

To access this setting, click the Security and Validation button on the Power XL tab.

The Enable Delete option allows users to permanently delete rows from the data table. If this checkbox is disabled, rows cannot be deleted using any other method in Reading View.

However, it's essential to note that pressing the buttons alone does not automatically save the changes. To finalize the action and ensure the changes are saved, users must click the Save Changes button. This step guarantees that any modifications made, including the insertion or deletion of rows, are committed. It ensures that changes are permanently saved.

Delete Selected Row(s)

To access this setting, click the Security and Validation button on the Power XL tab.

When enabled, you can delete the selected row by selecting either the entire row or a cell from that row, even if the cell is outside the data table. When disabled, the Delete action will delete the first row. This setting is available only when the Enable Delete checkbox is enabled.

Insert Security Measure

To access this setting, click the Security and Validation button on the Power XL tab.

Managing record insertion in Reading View is facilitated by the Insert Security Measure setting.

To utilize this setting, the SmartFilter visual must be added to the report with a DAX measure. Subsequently, in the input field of the setting, the name of the same DAX measure must be written in the following format:

"TableName.FieldName" (e.g., Table.InsertSecurity).

If the SmartFilter is absent or if the name of the DAX measure is misspelled, even if the DAX measure is configured in the setting, row insertion is automatically disabled.

The Insert Security values are as follows:

TRUE - If the value of the DAX measure is not 0 or true, then new row(s) cannot be inserted. In such cases, the Insert button does not appear on the top row.

FALSE - If the value of the DAX measure is null, 0, false, "false", "FALSE", or an empty string, then new row(s) can be inserted. In such cases, the Insert button appears on the top row.

Validate All Records

To access this setting, click the Security and Validation button on the Power XL tab and select the Validation tab.

This setting allows the user to control which records are affected by the Data Validation settings.

When the setting is ON: The Data validation is performed for all records. The Save Changes button will not allow saving until all records, including old and unmodified ones, pass the validation. Upon attempting to save and invalid value into the column, if the user clicks Save Changes button, an error message will appear.

When the setting is OFF:

  • The validation is only checked for modified and inserted records. The [Save Changes] button will not allow saving until the modified and inserted records pass the validation.
  • Row validation will not be triggered when a modified cell belongs to an inserted custom column.
  • New data validation rules can be saved by saving the layout and the report.
  • To use the OFF state of Validate All Records, at least one column needs to be set as the Key Column setting. If no key column is set and Validate All Records is OFF, a warning message will appear.
  • Upon attempting to save and invalid value into the column, if the user clicks Save Changes button, an error message will appear.

If the setting is switched from OFF to ON, the data validation is not reapplied, so the data validation must be set again.

Example of complex reports:

Consider a scenario where calculations always refer to a different row. Building the dependency tree takes significantly longer than activating the Validate All Records setting. If this setting is turned OFF, the calculation will process the entire table.

Additionally, when you modify a value, you might notice that typing becomes slow in the affected cells, even with a table containing just a hundred rows. Therefore, when the Validate All Records setting is disabled, be cautious with cell references, as referring to another row will trigger the validation of the entire table.

Data Validation

Data validation can be set up for columns in order to ensure that they contain data that agrees to set criteria. Data validation can be set on a column by selecting a cell in the column, navigating to the Data tab, and clicking on the following icon.

In the following pop-up window, the Validation Criteria can be set up and applied to the selected column by pressing OK.

In order to make the invalid data visible, the Circle Invalid Data setting should be enabled on the Data tab by activating the dropdown menu by clicking on the Data Validation setting.

As a result, all invalid data points (whole numbers that do not equal to 1) will be circled.

Detailed Validation Message Threshold

To access this setting, click the Security and Validation button on the Power XL tab and select the Validation tab.

The Detailed Validation Message Threshold field allows the specification of how many invalid cell coordinates should be visible in the data validation error message.

For instance, if the threshold value is set to 10, as shown in the following image, the coordinates of the first 10 invalid cells will be displayed in the error message.

By clicking more cell(s) in the message, you can view the coordinates about the additional invalid cells in a new dialog box.

The default value of the setting is 0. If you retain the default value, then the warning message appear as "Some of your cells are invalid".

Design and Layout

Display Buttons as

To access this setting, click the Design and Layout button on the Power XL tab.

The default value of Display Buttons as is Labels.

  • Labels - The top row buttons will be displayed as labeled buttons.

  • Icons - The top row buttons will be displayed as icons.

External Stylesheet

To access this setting, click the Design and Layout button on the Power XL tab.

The External Stylesheet setting provides an option to inject CSS code to customize any html element, except the canvas, where the sheets are displayed. Users can either supply a CSS file as a URL or simply paste inline CSS code. Both solutions are HTML-safe.

If a wrong URL is provided, an error message appears.

To ensure that formatting from CSS appears correctly, it may be necessary to include the !important command at the end of some CSS properties after adding the value.

For example:

example-visual-item {

color: white !important;

background-color: #5500ff !important;

}

 

Below are some CSS examples to style the buttons in Power XL Table.

}

.save-button {

background: #ffbf00 !important;

border-color: #ffbf00 !important;

border-width: 0px !important;

color: #000000 !important;

}

 

.save-button:hover {

background: #153050 !important;

border-color: #153050 !important;

border-width: 0px !important;

color: #ffffff !important;

}

To apply fill color and hover effects:

.discard-button {

background: #ffbf00 !important;

border-color: #ffbf00 !important;

border-width: 0px !important;

color: #000000 !important;

}

 

.discard-button:hover {

background: #153050 !important;

border-color: #153050 !important;

border-width: 0px !important;

color: #ffffff !important;

}

To change the labels, rename the Save Changes button to Save:

.save - button {

position: relative;

color: transparent;

}

.save - button::after {

content: "Save";

background - color: green;

color: white;

position: absolute;

top: 0;

left: 0;

width: 100 % ;

height: 100 % ;

display: flex;

align - items: center;

justify - content: center;

}

To hide the Delete button:

.control - buttons__button.delete-row - button {

display: none

}

To hide the tooltips:

.MuiTooltip - popper div {

display: none;

}

Discard Layout Changes After Save

To access this setting, click the Design and Layout button on the Power XL tab, and select the Layout tab.

When the setting is ON: After successfully saving to the database, any unsaved layout changes will be discarded, and the layout will revert to its last saved state.

Before successful save:

After successful save:

When the setting is OFF: After successfully saving to the database, any unsaved layout changes will be retained.

Data table before successful save:

Data table after successful save:

Always Show Insert Row

To access this setting, click the Design and Layout button on the Power XL tab, and select the Layout tab.

When this option is enabled, and if there is no data shown in the Power XL table within the current filter context, a new empty row will be displayed at the top. This empty row allows users to insert new values into the source table:

The row will not be displayed when there are already existing rows to show.

Show Clear All Filters

The Clear All Filters button enables the removal of all the filters that have been applied to the column headers in the visual.

To access this setting, click the Design and Layout button on the Power XL tab, and select the Layout tab.

By default, this setting is turned OFF.

The Clear All Filters button affects the Apply Filters to Entire Report setting. Consequently, using the Clear All Filters button removes filters not only from the current visual but also from other visuals in the report.

The Clear All Filter icon is shown in the following image.

Clear All Filter button is shown in the following image.

Discard Layout Changes After Visual Reload

To access this setting, click the Design and Layout button on the Power XL tab, and select the Layout tab.

The Discard Layout Changes After Visual Reload setting allows specifying a preference for saving data.

By default, this setting is OFF.

When turned ON, it affects how the visual handles unsaved layout modifications after specific events occur on the page.

The visual reloads and discards unsaved layout changes if any of the following actions occur:

  • Slicer Change: If a slicer is modified or adjusted.
  • Filter Application: When a filter is applied to the visual.
  • Cross Filtering: If cross-filtering occurs with a different visual.
  • Value Saving: This behaves similarly to the Discard Layout Changes After Save setting.

Data table before Visual Reload:

Data table after Visual Reload:

Show Status Bar in Reading View

To access this setting, click the Design and Layout button on the Power XL tab, and select the Layout tab.

Turn ON this setting to display the timestamp of the last successful save in the grid’s status bar when in reading mode. The status bar also displays the average, count, and summary values for the numbers selected in the grid.

Empty Row Count Below Table

To access this setting, click the Design and Layout button on the Power XL tab, and select the Layout tab.

This setting allows you to specify the number of additional rows to display beneath the data table based on your preference. The minimum value is 0 and the default value is 100.

Region

To access this setting, click the Design and Layout button on the Power XL tab, and select the Localization tab.

This setting allows you to specify the input format for decimals, thousand separators, and dates, displaying the column values accordingly. Select Auto Detect from the drop-down menu for the visual to identify the browser language and set the local format, or select any language/region of your preference. The default value is English (US).

Note that for date-type columns, you must delete the existing column in the grid and re-add it to apply the updated format.

Data Interaction

Power BI, as a default, can only render 30000 records. However, in case of more sizeable datasets, we need a way to bypass this limitation. Power XL Table has two settings that allow the user to control the amount of records that the visual renders:

  • Fetch All Before Load
  • Enable Lazy Loading

Fetch All Before Load

To access this setting, click the Data Interaction button on the Power XL tab.

By default, this setting is turned OFF.

  • OFF: The Power XL Table visual will load maximum 30.000 rows from the source table.
  • ON: The Power XL Table visual can load all data from the source table, even it has more than 30.000 rows. This may cause a longer loading time for the visual.

Enable Lazy Load

To access this setting, click the Data Interaction button on the Power XL tab.

The setting allows the Power XL Table visual to render records incrementally by specifying the size of the initially loaded segment and the size of the subsequently loaded segments. This setting bypasses the inherent limitation of Power Bi only being able to load 30000 records at most.

Enabling Lazy Loading will automatically turn off and disable Fetch All Before Load and enable the following settings:

  • Number of Initial Rows
    • Upon loading a report, the visual will render a number of records equal to the value of this setting.
  • Number of Rows Loaded on Scroll
    • The value of this setting must be between 15 and 30000. Trying to apply a value outside this interval will cause the value to return to either 15 or 30000.
    • Upon scrolling to the end of the initial segment, the visual will load a number of records equal to the value of this setting.

Apply Filters to Entire Report

To access this setting, click the Data Interaction button on the Power XL tab.

This setting allows the column filtering of the visual to be applied to all the other visuals on the same report page.

  • When the setting is OFF, only the visual associated with that column will be filtered. The filter will not affect other visuals in the report. Additionally, the filters will not be saved into the layout. This means they will not persist when the report is reopened or reloaded.
  • When the setting is ON, if a filter is applied to a column header, the filter effect will be applied to the entire report, affecting all visuals.

The setting is demonstrated in the use case below.

Before applying a Column Filter all records are visible in all the visuals of the report:

A filter is applied on the Product Subcategory column:

Upon pressing the OK button the filter is applied on the PXLT visual and all the other visuals of the report as well:

Row Selection

To access this setting, click the Data Interaction button on the Power XL tab.

Row Selection allows filtering records through a Master visual. By selecting a row in the Master visual, the Detail visual will re-render with the records that fit the ID of the rows selected in the Master visual.

In the following example, the master entity is the ProductSubcategory table and the detail entity is the Product table. The Product table has a many-to-one relationship with the ProductSubcategory table.

In order for this to work, the “Row Selection” setting must be set up in the Master visual.

The master visual, where the Row Selection setting is enabled, must include a Key Column (found in the Column Settings property group). Without a Key Column in the master visual's data table, the Row Selection feature will always filter out only the last row, regardless of the rows or sections the user intends to display.

Before Row Selection:

After Row Selection:

The Row Selection dropdown box has the following settings:

  • NONE - Disables Row Selection. The Detail visual will not re-render upon selecting rows in the Master Visual.
  • CELLS - Allows selecting a single cell or a range of cells to trigger Row Selection.
  • ENTIRE ROW - Row Selection is triggered only when one or more row headers are selected in the Master visual.
  • BOTH - Row Selection is triggered if one or more cell(s) or entire row(s) are selected in the Master visual.

Enable Import Data

To access this setting, click the Data Interaction button on the Power XL tab.

Upon enabling the setting and clicking OK, the Import Data button appears in the menu.

The Import Data button allows importing data using the following file extensions:

  • xlsx
  • csv (comma- or semicolon-separated)
  • tsv
Importing Data

To import the data, follow these steps:

  1. Click the Import Data button. The Upload File popup dailog window appears.

  1. Drag and drop the file into Upload window to upload the file. Alternatively, you can also search the file in your browser that you want to upload.
  2. Click the Import button to initiate the import process. The Import Finished message appears if the data import is successful.

If the file extension is not supported, an error message appears.

Import File Requirements

Before you import a file, ensure the file adheres to the following conditions::

  • Import files should consist of a single sheet only.
  • Data in the file must start from cell A1.
  • Include column headers in Row 1, matching the data table’s columns.
  • Formatting won’t be imported with the data.

  • Optionally, it can contain an operation column with keywords like “add,” “upd,” or “del” for adding, updating, and deleting data. The operation column is not mandatory

  • Remember, using the operation column is not mandatory.
  • If no operation column exists, the backend checks for existing records by ID.
    • If found, it is treated as an update.
    • If not found, the backend attempts to add the record.
Merging Data Examples

When merging data, consider the following scenarios:

  • If the imported Excel file contains two columns, but the data table in the visual has only one column, the extra column from the imported file will not be displayed. Only the column that matches the data table will appear. Example: If the Excel file has columns A and B, but the visual’s data table has only column A, only column A data will be shown.
  • When the visual’s data table has two columns, but the imported Excel file contains only one column, an error occurs during the import process. Ensure that all columns from the source table are included in the import file.
  • If there is no common column between the visual’s data table and the XLSX file, the import is considered invalid. In such cases, the backend will generate an error message: “No matching columns found.”
Error Handling

When an error occurs during the data import process, the following scenarios are handled:

  • If a fatal error occurs, a pop-up displays the corresponding error message. In such cases, the visual will not import the file.

  • When some records can be successfully imported, but there are invalid records in the imported file, users have an option.
    • They can download a report file that highlights the rows with errors.
    • The report file allows users to correct the problematic rows and import again.

Prevent Auto Refresh

To access this setting, click the Data Interaction button on the Power XL tab.

This setting blocks grid updates when you have unsaved changes. It prompts you to save or discard your edits, preventing unintentional loss. Turn on this setting to create a block period for automatic refresh.

Enable AI Features

See Enable AI Features for detailed information.

Data Export Settings

Enable XLSX Export / Enable PDF Export

To access these settings, click the Data Export Settings button on the Power XL tab.

When these settings are turned ON, the Export to XLSX and Export to PDF buttons will be displayed in the top row, positioned above the Designer Ribbon.

If operation buttons are displayed as Labels:

If operation buttons are displayed as Icons:

Debug

Diagnostic Mode

Enabling this setting will reveal debug settings.

Other features

Power XL Specific Formulas

USERNAME()

Returns the whole name of the current user (First name + Last name)

DOMAIN()

Returns the domain of the current user (e.g.: poweronbi.com)

USERPRINCIPALNAME()

Returns the whole email address of the current user.

IFISROWDIRTY()

Evaluates and returns its parameter when any field of the current row has been changed by the user.

IFISRANGEDIRTY()

Evaluates and returns its first parameter when any field within the specified range (as the second parameter) has been changed by the user.

CUBE.FILTER(TableName, ColumnName)

This formula is typically used for master-detail tables or in conjunction with SmartFilter.

A typical usage example is: =IFISROWDIRTY(CUBE.FILTER("SalesTest", "Customer ID"))

The CUBE.FILTER function retrieves all SmartFilter values associated with the provided Table and returns the value if there is only one unique value for the specified Field. However, if there are multiple values for the Field, the function returns null.

CUBE.FILTER supports column types such as Text, Number, Date, and Selection.

Note: The formula is not editable in Reading view for selection column types.

Implementation of Computed / Default Values

To implement the same functionality in Power XL Table as in the TableEditor visual, you can utilize the IFISROWDIRTY() formula. This formula allows you to set computed or default values based on whether a row has been modified by the user.

For example, let's say the Power XL Table has an audit trigger on the database side that requires capturing the current user's email address (modified by) and the current date and time (modified when).

To achieve this, follow these steps:

  1. Select the first row of the "Modified By" column and use the following formula:

    2. =IFISROWDIRTY(USERPRINCIPALNAME())

    This formula will populate the "Modified By" column with the email address of the user whenever they make changes to a row.

  2. Select the first row of the "Modified When" column and use the following formula:

    3. =IFISROWDIRTY(NOW())

    This formula will update the "Modified When" column with the current date and time whenever a user modifies a row.

The Power XL Table visual will automatically copy these formulas to all other rows. Once the configuration is complete, whenever a user makes any modification to a row, the "Modified By" column will be updated with their email address, and the "Modified When" column will be updated with the current date and time.

It's worth noting that not only can you use these functions as parameters, but you can also incorporate other formulas or calculations as needed.

❗ Please keep in mind, that when we support our clients and partners we are focusing on our product and not on Excel functionalities.

Was this article helpful?

We're sorry to hear that.