Skip to content

Dynamic Table Extractions with Power BI

This article shows how to create dynamic SAP table extractions using Xtract Universal and Microsoft Power BI Desktop by parameterizing WHERE Clauses in Power Query (M-scripts).

About WHERE Clauses in Power BI

When connecting Power BI to SAP via Xtract Universal, you can define static and/or dynamic WHERE Clauses in the M-script to filter table records. This article covers the following WHERE Clause scenarios:

  • Static WHERE Clauses in Power BI:
    A hardcoded filter condition in the M-script that does not change unless the query is manually modified. Static WHERE Clauses use fixed values to return corresponding table records. The following sample WHERE Clause returns records where values in the column GJAHR of SAP table ACDOCA equal 2025:

    ACDOCA~GJAHR EQ '2025'

    The WHERE Clause translates into the M-script as follows:

    Parameters = [ whereClause = "ACDOCA~GJAHR EQ '2025'"],

  • Dynamic WHERE Clauses using Power BI parameters:
    A WHERE Clause dynamically built in the M-script using Power BI parameters. This scenario allows changing filter values without editing the query logic. The following sample WHERE Clause returns records where values in the column GJAHR of SAP table ACDOCA equal the parameter pGJAHR:

    ACDOCA~GJAHR EQ @pGJAHR

    The WHERE Clause translates into the M-script as follows:

    Parameters = [ whereClause = "ACDOCA~GJAHR EQ " & pGJAHR],

  • Dynamic WHERE Clause using mapped parameters:
    A WHERE Clause defined and dynamically built inside Xtract Universal using custom runtime parameters mapped to Power BI parameters. In this scenario, the filter logic remains inside Xtract Universal while values are controlled in Power BI. The following sample WHERE Clause returns records, where values in the column FISCYEARPER range between the values of the parameters myFISCYEARPER_from and myFISCYEARPER_to:

    FISCYEARPER GE @myFISCYEARPER_from AND FISCYEARPER LT @myFISCYEARPER_to

    Map the Xtract Universalparameters to Power BI parameters in the M-script as follows:

    Parameters = [ myFISCYEARPER_from = pFISCYEARPER_from, myFISCYEARPER_to = pFISCYEARPER_to],

  • Dynamic WHERE Clauses using expressions:
    A WHERE Clause dynamically built in the M-script using M expressions. This scenario defines filter values according to defined rules. There is no need to manually change values, as the expression evaluates at refresh time. The following sample expression returns the current year as text:

    Text.From(Date.Year(DateTime.LocalNow()))

    The expression translates into the WHERE Clause in the M-script as follows:

    Parameters = [ whereClause = "ACDOCA~GJAHR EQ '" & Text.From(Date.Year(DateTime.LocalNow())) & "'"],

For better maintenance and reusability, use Power BI parameters instead of entering expressions directly into the M-script. To turn a parameter into a query, go to the list of queries and right-click the parameter. Select Convert to Query from the context menu and enter an expression. Example: = Text.From(Date.Year(DateTime.LocalNow())).

Refer to Documentation: WHERE Clause Syntax for information on:

  • how to write WHERE Clauses.
  • data formats for parameter values, e.g., YYYYMMDD for dates.

Set Up a Power Query in Power BI

Follow the steps below to set up a Power Query (M-script) to connect with Xtract Universal and query dynamic SAP data. This article focuses on Power BI Desktop; instructions vary for Power BI Service and Power BI Report Server:

  1. Create a new Report or edit an existing Report:
    • New Report: go to Home > Blank Report > Get Data > Blank Query.
    • Existing Report: open the Report and go to Home > Transform Data.
  2. Optional: Create Power BI parameters for your dynamic WHERE Clause in Home > Manage Parameters > New Parameter. To use an expression for the parameter value, convert the parameter into a query.
    power-bi-dynamic-parameter
  3. Open the Advanced Editor.
    Advanced Editor
  4. Copy the content of the loading_script.txt file into the editor.
  5. Change the values for ExtractionName and ServerURL to match the name of your Xtract Universal extraction name and server URL .
    PowerQueryEditor

  6. Enter values for Parameters to pass them to Xtract Universal. This includes passing whole WHERE Clauses:

    • Static WHERE Clause with fixed value:

      Parameters = [ whereClause = "ACDOCA~GJAHR EQ '2025'"],

    • Dynamic WHERE Clause, where pGJAHR is a Power BI parameter:

      Parameters = [ whereClause = "ACDOCA~GJAHR EQ " & pGJAHR],

    • Map Xtract Universal parameters (myFISCYEARPER_from and myFISCYEARPER_to) used in a WHERE Clause in Xtract Universal to Power BI parameters (pFISCYEARPER_from and pFISCYEARPER_to):

      Parameters = [ myFISCYEARPER_from = pFISCYEARPER_from, myFISCYEARPER_to = pFISCYEARPER_to],

    • Dynamic WHERE Clause with expressions:

      Parameters = [ whereClause = "ACDOCA~GJAHR EQ '" & Text.From(Date.Year(DateTime.LocalNow())) & "'"],

  7. Click [Done] to confirm the script .

  8. If prompted, provide an authentication method for the Power BI and Xtract Universal connection. For more information, see Single Sign On and SAP Authentication.
  9. Close the Power Query Editor and apply changes when prompted.
  10. Save the Report.

The new Power BI Report queries SAP data using Power BI parameters.

Last update: January 30, 2026