Skip to main content

Power XL Table Setup and Configuration

This topic outlines the steps to set up the Power XL Table visual to create a report.

Set Up the Basics

When Power BI Desktop is launched, a connection to a data source is established and the visuals (VPService, Power XL Table) are imported into your report. This section outlines the process of configuring the VPService visual and setting up the connection between the visuals and the Write-Back Service.

Configure the Visual

  1. After importing VPService, the following icon appears in the Visualizations tab. Right-click the icon and select About to review the version information.

  2. Click the icon to add the VPService visual to the report.
  3. Add a field to the VPService visual. Drag and drop the field from the Data tab to the Place Any Column Here section of the Visualizations tab.

    4. Important: If you have a mixed report where the source table is connected through a direct query in your model, and you also have additional tables in import mode, it is recommended to pull the arbitrary field into the VPService visual from one of the import mode tables for better performance.

  4. Click the icon to add the Power XL Table visual to the report.
  5. Add a field to the Power XL Table visual by dragging and dropping it under Columns. This will the display designer ribbon which includes the configuration options for the visual.

  6. Configure the Web Service URL of the VPService visual and the Writeback Service URL of the Power XL Table visual. These URLs must be identical.
    1. For the VPService visual, select the Format your visual icon in the Visualizations tab and then enter the URL under Web Service.

    2. For the Power XL Table visual, select Writeback Settings in the designer ribbon and then enter the Writeback Service URL.

Configure the Required Writeback Settings

  1. Select Writeback Settings in the Power XL tab of the designer ribbon.

  2. Configure the following settings:
    1. Writeback Service URL - Specify the URL that points to the hosting machine or the app service where it was installed. Typical URL: http(s)://WEBSERVICE_COMPUTER_NAME/PPWebservice/PPWebservice.svc. Ensure there is no extra slash at the end of the URL.
    2. Connection - Specify the name of the connection string defined in the web.config file of the Webservice, enter the connection string directly, or leave it blank, depending on the Data Type setting. The maximum length of this field is 250 characters.
    3. Data Type - Select the connection type used by the Write-Back Service.
      1. Data Connection Name (formerly SQL Datasource) - The connection is configured in the PPWebService web.config file. It should be referenced by its name in the Connection field.
      2. Model Connection Name (formerly SSAS Datasource) - Specify the connection name in the Connection field within the web.config of the web service.
      3. Data Connection String (formerly SQL) - Directly set the connection string in the Connection field. If the default SQLConnection is defined in the web.config, leave this field blank to automatically use that connection. This option is primarily used for testing and development purposes.
      4. Model Connection String (formerly SSAS) - Directly set the connection string in the Connection field. If the default SSASConnection is defined in the web.config of the web service, leave this field blank to automatically use that connection. This option is primarily used for testing and development purposes.
    4. Source Table - Specify the name of the target table for the Write-Back Service to identify it for data modifications. This field is case-sensitive. For instance, if fields from the Product table have been incorporated into the visual, the field value should match (Product), indicating the intention to save modifications to the Product table.

      Important: Ensuring the correct configuration of this field is crucial, any misconfiguration could result in save errors.

      Tables or columns in the report should not be renamed.

      For a Semantic model, the value must be the name of the entity specified in the model. For SQL-only data sources, the value should match the name of the SQL table utilized with the visual.

      When dealing with SQL connections where the table resides in a schema other than “dbo”, certain adjustments are necessary. When importing a table with a specific schema into a report, Power BI automatically attempts to rename the table. For example, if the hr.SalesPerson table is imported, it might appear in the Fields section as “Hr Sales Person” after renaming. This renaming process can pose challenges for Power XL Table in determining the appropriate table for initiating the write-back process. To address this issue, please follow these steps:

      1. Rename the table in the Fields list so that it contains exactly the actual SQL table name without the schema (in this example, SalesPerson).
      2. Remove and re-add the columns to the Power XL Table.
      3. Enter the fully qualified table name into the Source Table field in the format of schema.table (in this example, hr.SalesPerson).

Configure the Key Column

  1. Select Column Settings in the Power XL tab of the designer ribbon.
  2. Select the Key Columns checkbox to set the column representing the primary key of your source table. The primary key can be a single column or multiple columns, forming a composite key. In the case of a composite key, each column representing the primary key in the table must be configured as a Key Column.
  3. Set the numeric fields without aggregation to Don’t summarize in the Visualizations > Columns section.

    Important: This is crucial for ensuring proper functionality, particularly with an auto-increment primary key. Failure to configure this correctly will prevent changes to values in columns with defined aggregations.

    Aggregated values can be displayed in the visual, but these inputs cannot be modified.

Publish and Test the Report

After completing the report, publish it to either the Power BI Report Server or the Power BI service. Write-back functionality only operates once the report is published, so full functionality testing in Power BI Desktop during authoring is not possible.

Once published, modify a cell value and click the Save Changes button. The modifications should then be visible in the report.

By following these steps, you will create a simple, functional report with write-back capabilities using the Power XL Table visual.

Validate the Write-Back Service

After installation, it is advisable to verify that the Write-Back Service is properly installed. You can check this by navigating to the deployed URL in the following format: http(s)://yourserverName/PPWebService/PPWebService.svc

If you see the following page in your web browser, it indicates that the Write-Back Service is up and running.

Power XL Table Legend

This section provides an overview of the Power XL Table legend.

The designer ribbon can be collapsed by clicking the ^ symbol in its bottom right corner.

Helper tooltips appear if you hover above the following icons.

Was this article helpful?

We're sorry to hear that.