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, defining column data types, and producing the first report.

The connection works by authenticating against the QPR ProcessAnalyzer REST API with username and password 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 in the Power Query editor. The query performs three steps:

1. Get an access token: sends the username and password to the token endpoint and reads the returned access token.
2. Run an expression query: sends a QPR ProcessAnalyzer query to the api/expression/query endpoint using the access token.
3. Parse and shape: converts the returned data into a Power BI table.

Create Data Source and Semantic Model

Follow these steps to create the data source and the semantic model. These instructions have been written for the Power BI Service (cloud), but they can also be applied for the Power BI Desktop.

1. On the Home ribbon, click Get data → Blank query. The Power Query editor opens with a new empty query.
2. Delete any existing content and paste the Power Query script below. Configure server URL, username, password, model ID, the actual query, and column data types to the script. Click Next.
3. Click Configure Connection and Edit connection.
4. Check that Authentication kind is Anonymous, and Privacy level is NOT None (https://learn.microsoft.com/en-us/power-query/privacy-levels). Click Connect.
5. Define a describing name for the query (in the Query Settings panel).
6. Click Create a report.
7. Define a name for the semantic model, and click Create. Data source and semantic model have been created, and the report editor opens.)

Power Query Example

let
    // ================= Configuration =================
    Url  = "https://customer.onqpr.com/qprpa/",
    UserName = "qpr",
    Password = "demo",
    ModelId  = 1,
    RequestBody = [
        Dimensions = {
            [
                Name = "Case start time",
                Expression = "AggregateFrom(Events, ""Min"", TimeStamp)",
                DatetimeTruncation = "month"
            ]
        },
        Values = {
            [
                Name = "Count",
                AggregationFunction = "count"
            ]
        },
        Ordering = {
            [
                Name = "Case start time",
                Direction = "Ascending"
            ]
        },
        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(
        Url,
        [
            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(
        Url,
        [
            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 data types =================
    TypedTable = Table.TransformColumnTypes(
        AsTable,
        {
            {"Case start time", type datetime},
            {"Count", Int64.Type}
        }
    )
in
    TypedTable

Column Data Types

The data type for each column in the queried dataset needs to be defined. The following example shows how to convert QPR ProcessAnalyzer's data types in the Power Query script.

TypedTable = Table.TransformColumnTypes(
	AsTable,
	{
		{"TextColumn", type text},
		{"IntegerColumn", Int64.Type},
		{"DecimalColumn", type number},
		{"DateColumn", type datetime},
		{"BooleanColumn", type logical}
	}
),

Create Report

When the data source and the semantic model are in place, you can continue with creating the first report:

1. From the Visualizations pane, select a visual, for example a bar chart.
2. 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).
3. Adjust formatting (title, colors, data labels) in the Format pane.
4. Save the report: File → Save As.

Limitations

When implementing this integration, be aware of the following limitations.

Refreshing Data

Data shown in Power BI is a snapshot taken at the time of the last data load. It is not a live connection to QPR ProcessAnalyzer. The data is updated when a refresh is performed, either:

• Manual refresh: In Power BI Service, open the semantic model, and press the Refresh button. In Power BI Desktop, click Refresh on the Home ribbon.
• Scheduled refresh: Power BI Service allows to schedule an automatic refresh. To configure it, open the semantic model and press the arrow down below in the Refresh button and press Schedule refresh.

Shared QPR ProcessAnalyzer Credentials

The connection authenticates using a single set of QPR ProcessAnalyzer credentials embedded in the query, rather than each report user's individual account. This has important consequences:

• All Power BI users effectively see data through the same QPR ProcessAnalyzer account, regardless of who they are.
• User-specific permissions and per-user access restrictions defined in QPR ProcessAnalyzer are not applied to individual Power BI users.
• Any access control must be handled on the Power BI side (for example, workspace permissions or row-level security), not through the QPR ProcessAnalyzer.

Filtering Behavior

The configured query can contain QPR ProcessAnalyzer filters to cases and events, but creating filters interactively while viewing the report (drilldown) is not possible. Still, filtering performed on the Power BI side (slicers, visual filters, page/report filters) takes effect.

This means the full result set defined by the query is always retrieved before Power BI filtering is applied, which can affect the volume of data transferred and report performance. To limit data at the source, you must change the query itself rather than rely on interactive filtering.