Connect Microsoft Power BI to QPR ProcessAnalyzer

Connecting 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.

Prerequisites

Before you begin, make sure you have the following:

• Microsoft Power BI Desktop installed (latest version recommended).
• A valid QPR ProcessAnalyzer user account (username and password).
• The base URL of your QPR ProcessAnalyzer environment, for example:
  • https\://processanalyzer.onqpr.com/qprpa/
• The Model ID of the QPR ProcessAnalyzer model you want to query (a numeric identifier, for example 40477).
• Permission to access the target model and run expression queries.

Overview of the Connection

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 the Data Source (Blank Query)

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.

M Script (Web.Contents)

let
    // ================= Configuration =================
    BaseUrl  = "https://server.onqpr.com/qprpa/",
    ModelId  = 123,
    UserName = "qpr",
    Password = "demo",

    // ================= 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 the expression query =================
  RequestBody = [
        Dimensions = {
            [
                Name       = "Company Code",
                Expression = "Column(""SO: Company Code"")"
            ]
        },
        Values = {
            [
                Name                = "Count",
                AggregationFunction = "count"
            ]
        },
        Ordering = {
            [
                Name      = "Count",
                Direction = "Descending"
            ]
        },
        Root             = "Cases",
        ModelId          = ModelId,
        ContextType      = "model",
        ProcessingMethod = "dataframe"
    ],

    QueryResponse = Web.Contents(
        BaseUrl,
        [
            RelativePath = "api/expression/query",
            Headers = [
                #"Content-Type"  = "application/json",
                #"Accept"        = "application/json",
                #"Authorization" = "Bearer " & AccessToken
            ],
            Content = Json.FromValue(RequestBody)
        ]
    ),

    // ================= Step 3: Parse + shape =================
    Parsed = Json.Document(QueryResponse),
    AsTable = Table.FromRecords(Parsed)
in
    AsTable

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: Set Data Types for Each Column

QPR ProcessAnalyzer returns values as generic (often text) types. To ensure correct sorting, aggregation, and visualization, explicitly set the data type of each column.

1. In the Power Query Editor, select the query.
2. For each column, click the data type icon on the left of the column header and choose the correct type. Recommended types for the example query:

Column	Description	Recommended Data Type
Company Code	Dimension identifier	Text
Count	Aggregated case count	Whole Number

1. Alternatively, add the type change in the Advanced Editor by appending a step before in. For example:

Typed = Table.TransformColumnTypes(
AsTable,
{
{"Company Code", type text},
{"Count", Int64.Type}
}
)
in
Typed

1. When finished, on the Home ribbon click Close & Apply. Power BI loads the data and builds the semantic model.

Step 4: Review the Semantic Model

After loading, review and refine the semantic model:

1. Switch to the Model view (left sidebar).
2. Confirm your table (for example PA_CompanyCodeCounts) appears with the expected columns.
3. Select each column and, in the Properties pane, verify:
   • Data type matches the Power Query type.
   • Data category is set where relevant (e.g. mark geographic fields).
   • Summarization is set correctly (for example, set Count to Sum or Don't summarize as needed).
4. Optionally create measures (Home → New measure) for calculations such as totals or percentages. Example:

Total Cases = SUM('PA_CompanyCodeCounts'[Count])

1. If you load multiple tables, define relationships between them in the Model view.

Step 5: Create Your 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.

Customizing the Query

The RequestBody record defines what data QPR ProcessAnalyzer returns. Adjust the following fields:

Field	Purpose
Dimensions	List of grouping columns. Each has a Name (output column name) and an Expression (a QPR ProcessAnalyzer expression, e.g. Column("SO: Company Code")).
Values	List of aggregated measures. Each has a Name and an AggregationFunction (e.g. count, sum, avg).
Ordering	Sort order. References a Name and a Direction (Ascending or Descending).
Root	The analysis root, for example Cases or Events.
ModelId	The numeric model identifier.
ContextType	The query context, typically model.
ProcessingMethod	Processing mode, e.g. dataframe.

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.

Troubleshooting

Symptom	Possible Cause / Resolution
Formula.Firewall error	Privacy levels are blocking the combination of the token call and the query call. Configure privacy settings (see Step 2).
401 Unauthorized	Invalid username/password, or an expired/missing token. Verify credentials and that the account can access the model.
404 Not Found	Incorrect BaseUrl, RelativePath, or ModelId. Verify the URL (including trailing slash) and endpoints.
Credential prompt loops	Set the data source authentication to Anonymous and the correct privacy level.
Empty or incorrect table	Check the RequestBody dimensions/values and confirm the expressions match columns in the model.
Wrong sorting or aggregation	Ensure column data types are set correctly (see Step 3).

See Also

• QPR ProcessAnalyzer REST API documentation
• Microsoft Power Query Web.Contents function reference
• Power BI privacy levels documentation