This Custom Data Connector wraps many of the "Get" endpoints in the Power BI API (with the exception of the /executeQueries endpoint), 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.
Each function returns 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.).
- Open Power BI Desktop and navigate to File -> Options and Settings -> Options.
- 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.
- Close all Power BI Desktop instances on your local machine. You are often prompted to do so by Power BI Desktop.
- Copy the .mez file to your folder "Documents\Power BI Desktop\Custom Connectors". If the folder does not exist, create it first.
- Open Power BI Desktop.
- Select Get Data option.
- Navigate to the "Other" section and you should see the "Connect to Power BI REST API" connector.
- Select the connector and press the "Connect" button.
- You may be prompted with the pop-up below. Choose "Continue".
- 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.
- The Navigator prompt will appear (example below).
- Choose the "GetApps" option and you should see a json response (see example below).
- Then choose the "Transform Data" button. This should open the Power Query Editor.
- Under "Applied Steps", remove the steps "Invoked FunctionGetApps1" and "Navigation".
- You now will see a catalog of the Power BI REST APIs to leverage. I suggest you rename the Query "GetApps" to "Function Catalog".
- 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.
With the Function Catalog created, please follow these steps to leverage the functions:
- 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".
- The function will be created and it can now be used to query the Power BI service.
Not all functions from the Power BI REST API have been implemented. Here are the endpoints available at the moment.
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| GetDatasetQueryScaleOutStatusInGroup | Returns the query scale-out sync status for the specified dataset from the specified workspace. | Datasets - Get Query Scale Out Sync Status In Group |
| 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 |
| 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 |
| End Point | Description | MSDN Documentation |
|---|---|---|
| GetScorecardGoalsStatusRulesInGroup | Returns status rules of a goal. | GoalsStatusRules - Get |
| 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 |
| 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 |
| 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 |
| 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 |
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.
- Install Visual Studio code: https://code.visualstudio.com/download.
- Install Power Query SDK for Visual Studio Code: https://github.com/microsoft/vscode-powerquery-sdk
- Clone this repo to your local machine.
In order to the compile the custom data connector to the .mez file, please follow these instructions:
- 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".
-
If the build succeeds the .mez file will update in the folder "bin\AnyCPU\Debug".
-
If the build fails the Power Query SDK often presents a notification (see example below).
In order to test the custom data connector, please follow these instructions:
- Choose the "Set Credential" option within the Power Query SDK. Select AAD and follow the prompts to log into Microsoft 365.
- The .query.pq file is used to test the custom data connector, please update the section labeled "TEST VARIABLES" for your own environment.
- When you are ready to test, use the "Evaluate current file" option in the Power Query SDK in the "Explorer" tab.
- When the testing completes, a new tab will be present any failed results or if all the tests passed (example below).
