Microsoft Power BI

This page shows how to set up and use the Microsoft Power BI destination. The Microsoft Power BI destination loads data to Power BI. The setup in Xtract Universal.iQ consists of 3 main steps:

Supported Power BI Environments

There are two ways to connect your Power BI environment with Xtract Universal.iQ:

• Power BI Custom Connector
• Generic Power Query (M-script)

The graphic below shows which Power BI environments support which connection method.

Connect to Microsoft Power BI

To create a connection to Microsoft Power BI in Xtract Universal.iQ:

1. Open the Connections menu.
2. Click [Add Connection].
3. Enter a name in the Connection Name field.
4. Select Microsoft Power BI from the Connection Type dropdown. The corresponding settings open.
5. Click [Save].

The connection is now listed in the Connections menu and can be assigned to services.

Next, assign the destination to a service.

Assign Microsoft Power BI to a Service

To assign the Microsoft Power BI destination to a service:

1. Open the Services menu.
2. Select one or more services.
3. Click [ Edit].
4. Select an existing Microsoft Power BI connection from the Destination dropdown.
5. Click [Save].

When running the service, the results are written to the Microsoft Power BI.

Next, check if the service-specific destination settings match your needs and set up the connection in Power Automate.

Setup in Power BI

Connecting Xtract Universal.iQ with a Power BI environment requires a Custom Connector or a Power Query (M-script) in your Power BI environment.

Power BI Custom Connector

Download the Xtract Universal.iQ Power BI Custom Connector from the customer portal to connect to Power BI Service or Power BI Desktop. The connector follows Microsoft's guidelines for custom connectors in Power BI.

Install the Custom Connector

Load SAP Data using the Custom Connector

To use the Custom Connector to load data in Power BI:

1. Create a new data source in Power BI Desktop.
2. Select Xtract Universal.iQ from the Get Data menu.
3. Click [Connect].
4. Enter the server address of Xtract Universal.iQ using the following format: <Protocol>://<HOST or IP address>:<Port>/.
5. When prompted for authentication, enter your the authentication details.
6. Select an Xtract Universal.iQ service. The "Navigator" window only lists all extractions that use the Microsoft Power BI destination.
   
7. Click [Load]. This triggers a service run and writes the extracted SAP data to Power BI.

Your SAP data is now available for further processing in Power BI.

Power Query M-script

Download the Xtract Universal.iQ M-script template loading_script.txt to connect Xtract Universal.iQ to any Power BI environment.

Download loading_script.txt

Load SAP Data using the M-Script

To use the M-script and load data in Power BI:

1. Create a new Power BI Report using Blank Query as a data source.
2. Open the [Advanced Editor] and copy the content of the loading_script.txt file into the editor.

3. Change the values for ExtractionName and ServerURL to match the name of your Xtract Universal.iQ service and server URL:

   let
       ExtractionName = "mara",
       ServerUrl = "http://localhost:8065",
   
       // Record containing run parameters with corresponding values, can be empty
       // Usage: <XU parameter name>= <value or Power BI parameter>
       // MUST NOT use "name" as a record field here
       Parameters = [ /*rows= "300", myparameter= SomePowerBIParameter*/ ],
   

4. Optional: Enter values for Parameters to pass them to Xtract Universal.iQ. For a complete list of possible parameters, see Parameters.

5. Apply the script. If prompted, provide an authentication method for the connection.

Your SAP data is now available for further processing in Power BI.

Destination Settings

Destination settings define how and where a service saves its output data, such as the target folder and filename.

To open the destination settings:

1. Open the Services menu.
2. Select a service that uses the Microsoft Power BI connection.
3. Click [ Edit]. The service settings open.
4. Open the tab Destination Settings.

The destination settings for Microsoft Power BI are open and can be modified.

Power BI Settings

Column Name Format

Defines the format of the column name. The following options are available:

Date Conversion

Convert SAP Dates

Converts the SAP date format (YYYYMMDD) to a regular date format (YYYY-MM-DD), e.g., 19900101 becomes 1990-01-01. Target data uses a real date data-type and not the string data-type to store dates.

Replace SAP Dates 0000XXXX with

Converts the SAP date 00000000 to the entered value.

Replace SAP Dates 9999XXXX with

Converts the SAP date 9999XXXX to the entered value.

Replace Invalid SAP Dates with

If an SAP date cannot be converted to a valid date format, the invalid date is converted to the entered value. NULL is supported as a value. When converting the SAP date the two special cases 00000000 and 9999XXXX are checked first.

Related Links

• Knowledge Base Article: Dynamic Table Extractions with Power BI