Power Query Custom Data Connector for Power BI REST APIs (Commercial)

This Custom Data Connector wraps many of the "Get" endpoints in the Power BI API (including dataset query endpoints), so that OAuth can be used to authenticate to the service. This connector serves as a way to have a library of Power Query functions to build datasets based on the Power BI APIs without the need for storing client secrets or passwords in the dataset.

Most functions return a JSON body and not a table of data. This decision was made to provide flexibility in converting the JSON body to tabular data when 1) the API responses are changed by Microsoft or 2) the API responses differ between commercial and sovereign clouds (e.g., GCC, DoD, etc.).

The exceptions are ExecuteDaxQueries and ExecuteDaxQueriesInGroup, which return a list of Power Query tables (one table per EVALUATE result set, each preserving its own schema). These call the executeDaxQueries endpoint, auto-detect Apache Arrow IPC responses, and parse them directly (falling back to JSON parsing only when the response itself is JSON). Index the list (e.g. {0}) or iterate it. See Arrow IPC Support and the call tree below.

Table of Contents

1. Installation
   1. Desktop
   2. Using Functions
   3. Functions Implemented
   4. Arrow IPC Support (ExecuteDaxQueries)
   5. ExecuteDaxQueries Call Tree
   6. On-Premises Gateway
2. Building Connector
   1. Testing Connector

Installation

Desktop

1. Open Power BI Desktop and navigate to File -> Options and Settings -> Options.
2. Navigate to GLOBAL -> Security and under "Data Extensions" choose "Allow any extension..."

Because this is a custom data connector you have to choose this option in order to use it in Power BI Desktop.

3. Close all Power BI Desktop instances on your local machine. You are often prompted to do so by Power BI Desktop.
4. Copy the .mez file to your folder "Documents\Power BI Desktop\Custom Connectors". If the folder does not exist, create it first.
5. Open Power BI Desktop.
6. Select Get Data option.
7. Navigate to the "Other" section and you should see the "Connect to Power BI REST API" connector.

8. Select the connector and press the "Connect" button.
9. You may be prompted with the pop-up below. Choose "Continue".

10. If this is your first time using the custom data connector you will be prompted to sign into Office 365. Please follow the instructions to sign in and then choose the "Connect" button.

10. The Navigator prompt will appear (example below).

11. Choose the "GetApps" option and you should see a json response (see example below).

12. Then choose the "Transform Data" button. This should open the Power Query Editor.

13. Under "Applied Steps", remove the steps "Invoked FunctionGetApps1" and "Navigation".

14. You now will see a catalog of the Power BI REST APIs to leverage. I suggest you rename the Query "GetApps" to "Function Catalog".

15. I suggest you also uncheck "Enable Load" for the Function Catalog so it doesn't appear in the data model. When disabled the Function Catalog will appear italicized.

Using Functions

With the Function Catalog created, please follow these steps to leverage the functions:

1. Identify the name of the function you wish to use. Right-click on the "Function" value located for the appropriate row and select "Add as New Query".

2. The function will be created and it can now be used to query the Power BI service.

Functions Implemented

Not all functions from the Power BI REST API have been implemented. Here are the endpoints available at the moment.

Apps

End Point	Description	MSDN Documentation
GetApp	Returns the specified installed app.	Apps - Get App
GetApps	Returns a list of installed apps.	Apps - Get Apps
GetDashboardInApp	Returns the specified dashboard from the specified app.	Apps - Get Dashboard
GetDashboardsInApp	Returns a list of dashboards from the specified app.	Apps - Get Dashboards
GetReportInApp	Returns the specified report from the specified app.	Apps - Get Reports
GetReportsInApp	Returns a list of reports from the specified app.	Apps - Get Reports
GetTileInApp	Returns the specified tile within the specified dashboard from the specified app. Supported tiles include datasets and live tiles that contain an entire report page.	Apps - Get Tile
GetTilesInApp	Returns a list of tiles within the specified dashboard from the specified app.	Apps - Get Tiles

Dashboards

End Point	Description	MSDN Documentation
GetDashboardInGroup	Returns the specified dashboard from the specified workspace.	Dashboards - Get Dashboard In Group
GetDashboardsInGroup	Returns a list of dashboards from the specified workspace.	Dashboards - Get Dashboards In Group
GetDashboardTileInGroup	Returns the specified tile within the specified dashboard from the specified workspace. Supported tiles include datasets and live tiles that contain an entire report page.	Dashboards - Get Dashboard Tile In Group
GetDashboardTilesInGroup	Returns a list of tiles within the specified dashboard from the specified workspace. Supported tiles include datasets and live tiles that contain an entire report page.	Dashboards - Get Dashboard Tiles In Group

Dataflows

End Point	Description	MSDN Documentation
GetDataflowInGroup	Exports the specified dataflow definition to a JSON file.	Dataflows - Get Dataflow
GetDataflowDataSourcesInGroup	Returns a list of data sources for the specified dataflow.	Dataflows - Get Dataflow Data Sources
GetDataflowTransactionsInGroup	Returns a list of transactions for the specified dataflow.	Dataflows - Get Dataflow Transactions
GetDataflowsInGroup	Returns a list of all dataflows from the specified workspace.	Dataflows - Get Dataflows
GetUpstreamDataflowsInGroup	Returns a list of upstream dataflows for the specified dataflow.	Dataflows - Get Upstream Dataflows In Group

Datasets

End Point	Description	MSDN Documentation
GetDatasetDiscoverGatewaysInGroup	Returns a list of gateways that the specified dataset from the specified workspace can be bound to.	Datasets - Discover Gateways In Group
ExecuteQuery	Executes a single Data Analysis Expressions (DAX) query against a dataset.	Dataset - Execute Queries
ExecuteQueryInGroup	Executes a single Data Analysis Expressions (DAX) query against a dataset within a workspace.	Dataset - Execute Queries In Group
ExecuteDaxQueries	Executes DAX queries against a dataset and attempts Arrow IPC response detection/parsing to return native tables.	Dataset - Execute Dax Queries
ExecuteDaxQueriesInGroup	Executes DAX queries in a workspace dataset and attempts Arrow IPC response detection/parsing to return native tables.	Dataset - Execute Dax Queries In Group
GetDatasetInGroup	Returns the specified dataset from the specified workspace.	Datasets - Get Dataset In Group
GetDatasetToDataflowsLinksInGroup	Returns a list of upstream dataflows for datasets from the specified workspace.	Datasets - Get Dataset To Dataflows Links In Group
GetDatasetUsersInGroup	Returns a list of principals that have access to the specified dataset.	Datasets - Get Dataset Users In Group
GetDatasetsInGroup	Returns a list of datasets from the specified workspace.	Datasets - Get Datasets In Group
GetDatasetDatasourcesInGroup	Returns a list of data sources for the specified dataset from the specified workspace.	Datasets - Get Datasources In Group
GetDatasetDirectQueryRefreshScheduleInGroup	Returns the refresh schedule for a specified DirectQuery or LiveConnection dataset from the specified workspace.	Datasets - Get Direct Query Refresh Schedule In Group
GetDatasetParametersInGroup	Returns a list of parameters for the specified dataset from the specified workspace.	Datasets - Get Parameters In Group
GetDatasetRefreshExecutionDetailsInGroup	Returns execution details of an enhanced refresh operation for the specified dataset from the specified workspace.	Datasets - Get Refresh Execution Details In Group
GetDatasetRefreshHistoryInGroup	Returns the refresh history for the specified dataset from the specified workspace.	Datasets - Get Refresh History
GetDatasetRefreshScheduleInGroup	Returns the refresh schedule for the specified dataset from the specified workspace.	Datasets - Get Refresh Schedule In Group
GetDatasetSyncStatusInGroup	Returns the sync status information of the read-only replica and read/write dataset. (Preview: Power BI Dataset Scale-Out)	Datasets - Get Dataset Sync Status In Group

Arrow IPC Support (ExecuteDaxQueries)

This connector attempts Arrow IPC detection/parsing for ExecuteDaxQueries and ExecuteDaxQueriesInGroup responses and returns a list of Power Query tables (one per EVALUATE result set).

Supported and validated today:

• Arrow response detection by content type and/or Arrow magic bytes.
• DAX JSON response parsing for non-Arrow payloads from ExecuteDaxQueries* endpoints.
• Dictionary-encoded columns, including recursive dictionary dependencies across dictionary batches.
• Dictionary delta and replacement semantics in parser logic.
• LZ4_FRAME per-buffer body compression (Arrow BodyCompression codec 0), decompressed in pure M and applied to both record and dictionary batches. This is what real executeDaxQueries responses use for wide/large results.
• date64 (millisecond) and date32 (day) column decoding.
• Deterministic parity validation against ExecuteQuery* for representative fixtures including:
  • numbers
  • booleans
  • text
  • date/datetime shapes
  • blank/null values
• Endpoint behavior contract: no fallback from ExecuteDaxQueries* to ExecuteQuery*.

Current limitations and explicit non-support:

• ZSTD-compressed Arrow bodies (BodyCompression codec 1) are not supported and fail fast with an Unsupported Arrow compression codec diagnostic.
• Primitive Arrow kinds outside implemented decoding paths fail fast with explicit Unsupported primitive Arrow type errors.
• Unsupported or malformed dictionary metadata fails fast with actionable diagnostics.
• Arrow parsing support is scoped to connector-tested scenarios; unvalidated Arrow feature families (for example, uncommon extension/layout combinations) are not guaranteed.

Target Arrow format baseline:

• The parser is implemented against the current connector's Flatbuffers/IPC interpretation used by Power BI ExecuteDaxQueries* responses.
• Compatibility is validated through the project's targeted parity tests and Arrow reliability gate rather than a broad claim of full Apache Arrow specification coverage.

ExecuteDaxQueries Call Tree

Both ExecuteDaxQueries and ExecuteDaxQueriesInGroup share the same request/response pipeline; they differ only in the REST path (.../datasets/{id}/executeDaxQueries vs .../groups/{groupId}/datasets/{id}/executeDaxQueries). The response is buffered, its kind is detected, and it is routed to either the Arrow parser or the JSON parser. There is no fallback to ExecuteQuery* — a parse failure surfaces as an actionable error.

flowchart TD
    A["ExecuteDaxQueries(datasetId, query, ...)"] --> P
    B["ExecuteDaxQueriesInGroup(groupId, datasetId, query, ...)"] --> P
    P["BuildExecuteDaxRequestPayload<br/>(drops null options)"] --> PD["PostExecuteDaxList(params)"]
    PD --> WC["Web.Contents → .../executeDaxQueries<br/>Accept: arrow.stream, arrow.file, octet-stream, json"]
    WC --> BB["Binary.Buffer(response)"]
    BB --> RT["ExecuteDaxResponseAsTableList(bytes, headers)"]
    RT --> DK["ExecuteDaxDetectResponseKind<br/>ArrowDetectionIsArrowResponse:<br/>content-type or Arrow magic bytes"]
    DK -->|Arrow| AR["ArrowFromBinaryTables"]
    DK -->|JSON| JS["ExecuteDaxJsonToTableList"]
    AR --> PS["ArrowParseStreamTables"]
    PS --> PM["ArrowParseMessage<br/>Schema / DictionaryBatch / RecordBatch"]
    PS --> RDB["ArrowResolvePendingDictionaryBatches<br/>→ ArrowParseDictionaryBatch"]
    PS --> RB["ArrowRecordBatchToTable"]
    RB --> DCB["ArrowDecompressBatchBuffers<br/>LZ4_FRAME per buffer (codec 0)"]
    RB --> DEC["ArrowDecodeColumn<br/>(per column, dictionary-aware)"]
    AR --> T["list of Power Query tables<br/>(one per EVALUATE result set)"]
    JS --> T

Loading

GoalValues (Preview)

End Point	Description	MSDN Documentation
GetScorecardGoalValuesInGroup	Reads goal value check-ins.	GoalValues - Get
GetScorecardGoalValueInGroup	Reads a goal value check-in by a UTC date timestamp.	GoalValues - Get By ID

Goals (Preview)

End Point	Description	MSDN Documentation
GetScorecardGoalsInGroup	Returns a list of goals from a scorecard.	Goals - Get
GetScorecardGoalInGroup	Returns a goal by ID from a scorecard.	Goals - Get By ID
GetScorecardGoalRefreshHistoryInGroup	Reads refresh history of a connected goal.	Goals - Get Refresh History

Goals Status Rules (Preview)

End Point	Description	MSDN Documentation
GetScorecardGoalsStatusRulesInGroup	Returns status rules of a goal.	GoalsStatusRules - Get

Groups

End Point	Description	MSDN Documentation
GetGroupUsers	Returns a list of users that have access to the specified workspace.	Groups - Get Group Users
GetGroups	Returns a list of workspaces the user has access to.	Groups - Get Groups

Pipelines

End Point	Description	MSDN Documentation
GetPipeline	Returns the specified deployment pipeline.	Pipelines - Get Pipeline
GetPipelineOperation	Returns the details of the specified deploy operation performed on the specified deployment pipeline, including the deployment execution plan.	Pipelines - Get Pipeline Operation
GetPipelineOperations	Returns a list of the up-to-20 most recent deploy operations performed on the specified deployment pipeline.	Pipelines - Get Pipeline Operations
GetPipelineStageArtifacts	Returns the supported items from the workspace assigned to the specified stage of the specified deployment pipeline.	Pipelines - Get Pipeline Stage Artifacts
GetPipelineStages	Returns the stages of the specified deployment pipeline.	Pipelines - Get Pipeline Stages
GetPipelineUsers	Returns a list of users that have access to the specified deployment pipeline.	Pipelines - Get Pipeline Users
GetPipelines	Returns a list of deployment pipelines that the user has access to.	Pipelines - Get Pipelines

Reports

End Point	Description	MSDN Documentation
GetPaginatedReportDatasourcesInGroup	Returns a list of data sources for the specified paginated report (RDL) from the specified workspace.	Reports - Get Datasources In Group
GetPageInGroup	Returns the specified page within the specified report from the specified workspace.	Reports - Get Page In Group
GetPagesInGroup	Returns a list of pages within the specified report from the specified workspace.	Reports - Get Pages In Group
GetReportInGroup	Returns the specified report from the specified workspace.	Reports - Get Report In Group
GetReportsInGroup	Returns a list of reports from the specified workspace.	Reports - Get Reports In Group
ExportReportInGroup	Exports the specified report from the specified workspace to a Power BI .pbix or .rdl file.	Reports - Export Report In Group

Scorecards (Preview)

End Point	Description	MSDN Documentation
GetScorecardsInGroup	Returns a list of scorecards from a workspace.	Scorecards - Get
GetScorecardInGroup	Returns a scorecard with ID.	Scorecards - Get By ID
GetScorecardByReportIdInGroup	Reads a scorecard associated with an internal report ID.	Scorecards - Get Scorecard By Report Id

On-Premises Gateway

The custom data connector will need to be installed in the a Power BI Gateway in order to refresh datasets leveraging this custom connector. For more information on installing a custom data connector with a gateway please see: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-custom-connectors.

Building Connector

Prerequisites

1. Install Visual Studio code: https://code.visualstudio.com/download.
2. Install Power Query SDK for Visual Studio Code: https://github.com/microsoft/vscode-powerquery-sdk
3. Clone this repo to your local machine.

Compile

In order to the compile the custom data connector to the .mez file, please follow these instructions:

1. Using your keyboard, use the shortcut Ctrl+Shift+B. Visual Studio will prompt you within the command palette to choose a build task. Select the "build: Build connector project using MakePQX".

2. If the build succeeds the .mez file will update in the folder "bin\AnyCPU\Debug".

3. If the build fails the Power Query SDK often presents a notification (see example below).

Testing Connector

In order to test the custom data connector, please follow these instructions:

1. Choose the "Set Credential" option within the Power Query SDK. Select AAD and follow the prompts to log into Microsoft 365.

2. Copy the template file CI/Scripts/variables.test.template.json to CI/Scripts/variables.test.json.

   PowerShell:

   Copy-Item .\\CI\\Scripts\\variables.test.template.json .\\CI\\Scripts\\variables.test.json

   Keep variables.test.json local to your machine and update values for your own environment.

3. Run the split test suite from PowerShell:

   .\\CI\\Scripts\\Run-PQTests.ps1 -Compile $False

   The script runs each test group separately and reports which .query.pq file failed.

   To run a specific test file only:

   .\\CI\\Scripts\\Run-PQTests.ps1 -Compile $False -TestFileName PBIRESTAPIComm.tests.datasets.query.pq

   To repeatedly validate DateDim Arrow parsing (including in-group path) and catch intermittent failures:

   .\\CI\\Scripts\\Run-DateDimArrowSoak.ps1 -Iterations 5

   The soak script writes per-iteration logs to artifacts/arrow-soak/ and fails immediately on the first failed iteration.

   To enforce the full Arrow parsing reliability gate in one command:

   .\\CI\\Scripts\\Run-ArrowParsingGate.ps1 -SoakIterations 5

   The gate runs parity, connector proof, arrow helper tests, and soak validation, then writes a summary to artifacts/arrow-gate/.

4. When testing completes, review the per-file pass/fail output and the final summary table.

5. For reliable CI execution with targeted test files in GitHub Actions, follow the workflow guide in docs/GITHUB-ACTIONS-TESTING.md.