Connect Microsoft Power BI to QPR ProcessAnalyzer

This guide explains how to connect Microsoft Power BI to QPR ProcessAnalyzer using the Power Query Web.Contents function. It walks you through creating the data source, building the semantic model, setting privacy levels, defining column data types, and producing your first report.

The connection works by authenticating against the QPR ProcessAnalyzer REST API to obtain an access token, then running an expression query against a selected model to retrieve data as a table.

Overview

The connection is built entirely in the Power Query editor using a single M query. The query performs three steps:

1. Get an access token: sends the username and password to the token endpoint using the OAuth password grant type and reads the returned access_token.
2. Run an expression query: sends a JSON request body (dimensions, values, ordering, etc.) to the api/expression/query endpoint, using the access token as a Bearer token.
3. Parse and shape: converts the returned JSON into a Power BI table.

Step 1: Create Data Source

1. Open Power BI Desktop.
2. On the Home ribbon, click Get data → Blank query.
   • Alternatively, choose Get data → More... → Other → Blank Query → Connect.
3. The Power Query Editor opens with a new empty query named Query1.
4. In the toolbar, click Advanced Editor.
5. Delete any existing content and paste the M script below.

Power Query Script

let
    // ================= Configuration =================
    BaseUrl  = "https://server.onqpr.com/qprpa/",
    ModelId  = 123,
    UserName = "qpr",
    Password = "demo",
    RequestBody = [
        Dimensions = {
            [
                Name       = "Company Code",
                Expression = "Column(""Company Code"")"
            ]
        },
        Values = {
            [
                Name                = "Count",
                AggregationFunction = "count"
            ]
        },
        Ordering = {
            [
                Name      = "Count",
                Direction = "Descending"
            ]
        },
        Root             = "Cases",
        ModelId          = ModelId,
        ContextType      = "model",
        ProcessingMethod = "dataframe"
    ],

    // ================= Step 1: Get access token =================
    TokenBody = Uri.BuildQueryString([
        grant_type = "password",
        username   = UserName,
        password   = Password
    ]),
    TokenResponse = Web.Contents(
        BaseUrl,
        [
            RelativePath = "token",
            Headers = [
                #"Content-Type" = "application/x-www-form-urlencoded",
                #"Accept"       = "application/json"
            ],
            Content = Text.ToBinary(TokenBody)
        ]
    ),
    TokenParsed = Json.Document(TokenResponse),
    AccessToken = TokenParsed[access_token],

    // ================= Step 2: Run query =================
    QueryResponse = Web.Contents(
        BaseUrl,
        [
            RelativePath = "api/expression/query",
            Headers = [
                #"Content-Type"  = "application/json",
                #"Accept"        = "application/json",
                #"Authorization" = "Bearer " & AccessToken
            ],
            Content = Json.FromValue(RequestBody)
        ]
    ),
    Parsed = Json.Document(QueryResponse),
    AsTable = Table.FromRecords(Parsed),

    // ================= Step 3: Set column types =================
    TypedTable = Table.TransformColumnTypes(
        AsTable,
        {
            {"Company Code", type text},
            {"Count",        Int64.Type}
        }
    )
in
    TypedTable

1. Update the Configuration section to match your environment:
   • BaseUrl – your QPR ProcessAnalyzer base URL (keep the trailing slash).
   • ModelId – the numeric ID of your model.
   • UserName and Password – your QPR ProcessAnalyzer credentials.
2. Adjust the RequestBody to select the dimensions, values, and ordering you need (see Customizing the Query).
3. Click Done to close the Advanced Editor.
4. Rename the query (right-click the query in the Queries pane → Rename), for example to PA_CompanyCodeCounts.

Step 2: Configure Privacy Settings

Because the query passes data (credentials and a token) from one Web.Contents call into another, Power BI's Privacy Level checks can block the query or trigger a "Formula.Firewall" error. You must configure privacy settings correctly.

1. In the Power Query Editor, go to File → Options and settings → Options.
2. Under Current File, select Privacy.
3. Choose Combine data according to your Privacy Level settings for each source, and set the source privacy level to Public or Organizational as appropriate for your organization.
4. Click OK.

Setting credentials for the data source

When the query runs for the first time, Power BI may prompt for credentials for the base URL:

1. If prompted, select Anonymous as the authentication method (authentication is handled inside the query via the token).
2. Set the privacy level for the URL to Organizational (or Public).
3. Click Connect.

Step 3: Create First Report

1. Switch to the Report view (left sidebar).
2. From the Visualizations pane, select a visual, for example a Clustered bar chart.
3. From the Data pane, drag fields onto the visual:
   • Drag Company Code to the Y-axis (or Axis).
   • Drag Count to the X-axis (or Values).
4. Adjust formatting (title, colors, data labels) in the Format pane.
5. Add slicers or filters as needed (for example, a slicer on Company Code).
6. Save the report: File → Save As and choose a location for the .pbix file.

Refreshing Data

• Manual refresh: In Power BI Desktop, click Refresh on the Home ribbon.
• Scheduled refresh (Power BI Service): After publishing, configure a scheduled refresh. Because the query calls a web API, you may need an appropriate gateway configuration and correctly configured credentials/privacy levels in the service.