The Power BI Tracker revolutionizes BI administration by providing visibility into dataset refresh status across all your workspaces. Instead of manually checking individual datasets, this solution automatically monitors your entire Power BI environment and highlights issues in an intuitive dashboard format.
1. Register Azure App for Power BI
Navigate to the Azure Portal at https://portal.azure.com and access App registrations. Click "New registration" to create a new app registration specifically for Power BI REST API access.
This Azure app will serve as the authentication mechanism for your Power BI Tracker, allowing programmatic access to Power BI REST APIs without requiring individual user credentials.
2. Configure Azure App Permissions
Navigate to "API permissions" within your newly created app registration. Click "Add a permission" and select "Power BI Service" from the list of available APIs.
Add the following delegated permissions:
Workspace.Read.All
Dataset.Read.All
Tenant.Read.All (for comprehensive workspace access)
After adding these permissions, click "Grant admin consent" to ensure the application has the necessary permissions to access Power BI data across your organization.
3. Generate Client Secret
In the Azure app registration, navigate to "Certificates & secrets" and click "New client secret". Provide a meaningful description such as "Power BI REST API" and set an appropriate expiration period.
Copy and securely store the generated client secret value immediately, as it won't be displayed again. This secret will be used in your Power Query functions to authenticate API calls.
4. Add App as Member to Power BI Workspace
In Power BI Service, navigate to the workspace where you want to deploy your tracking solution. Click the workspace settings and select "Access". Add your Azure app as a member by searching for the app name you created in step 1.
This step ensures your Azure app has the necessary permissions to read workspace and dataset information programmatically.
5. Create Access Token Function in Power Query
Create a new Power Query function called "AccessTokenPowerBI" that handles Azure AD authentication:
() =>
let
TenantId = "XXXXXXXXXXXX", // your TenantId from step #2
ClientId = "XXXXXXXXXXXX", // your ClientId from step #2
ClientSecret = "XXXXXXXXX", // your ClientSecret from step #3
Body = "grant_type=client_credentials"
& "&client_id=" & ClientId
& "&client_secret=" & ClientSecret
& "&resource=https://analysis.windows.net/powerbi/api",
Response = Json.Document(
Web.Contents(
"https://login.microsoftonline.com/" & TenantId & "/oauth2/token",
[
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(Body)
]
)
),
AccessToken = Response[access_token]
in
AccessTokenThis function establishes the authentication foundation for all subsequent API calls.
6. Create Workspace Retrieval Function
Build a "GetWorkspaces" function that retrieves all accessible Power BI workspaces:
(accessToken as text) =>
let
Zdroj = Json.Document(
Web.Contents(
"https://api.powerbi.com/v1.0/myorg",
[
RelativePath = "/groups",
Headers = [ Authorization = "Bearer " & accessToken ]
]
)
),
value = Zdroj[value]
in
ZdrojThis function returns a comprehensive list of all workspaces your Azure app can access.
7. Create Dataset Retrieval Function
Develop a "GetDatasets" function that extracts datasets from each workspace:
(group as text, optional accessToken as text) =>
let
Zdroj = Json.Document(
Web.Contents(
"https://api.powerbi.com/v1.0/myorg",
[
RelativePath = "/groups/" & group & "/datasets",
Headers = [ Authorization = "Bearer " & accessToken ]
]
)
),
value = Zdroj[value]
in
valueThis function iterates through each workspace to collect dataset information systematically.
8. Create Refresh History Function
Build a "GetRefreshes" function that captures refresh history for each dataset:
(group as text, optional dataset as text, optional accessToken as text) =>
let
Source = Json.Document(
Web.Contents(
"https://api.powerbi.com/v1.0/myorg/",
[
RelativePath = "groups/" & group & "/datasets/" & dataset & "/refreshes",
Headers = [ Authorization = "Bearer " & accessToken ]
]
)
),
value = Source[value]
in
valueThis function provides detailed refresh status information including start time, end time, and success status.
9. Combine All Functions
Create a comprehensive query that binds all functions together to create your complete tracking dataset.
let
// Part 1: Get Access Token and Workspaces
GetAccessToken = AccessTokenPowerBI(),
TokenTable = #table(1, { GetAccessToken }),
TokenColumnRenamed = Table.RenameColumns(
TokenTable,
{ { "Column1", "AccessToken" } }
),
WorkspacesAdded = Table.AddColumn(
TokenColumnRenamed,
"Workspaces",
each GetWorkspaces([AccessToken])
),
WorkspacesRecordExp = Table.ExpandRecordColumn(
WorkspacesAdded,
"Workspaces",
{ "value" },
{ "Workspaces" }
),
WorkspacesListExp = Table.ExpandListColumn(
WorkspacesRecordExp,
"Workspaces"
),
WorkspaceFieldsExp = Table.ExpandRecordColumn(
WorkspacesListExp,
"Workspaces",
{ "id", "name" },
{ "WorkspaceId", "WorkspaceName" }
),
// Part 2: Get Datasets and Refreshes
DatasetsAdded = Table.AddColumn(
WorkspaceFieldsExp,
"Datasets",
each GetDatasets([WorkspaceId], [AccessToken])
),
DatasetsListExp = Table.ExpandListColumn(
DatasetsAdded,
"Datasets"
),
DatasetFieldsExp = Table.ExpandRecordColumn(
DatasetsListExp,
"Datasets",
{ "id", "name" },
{ "DatasetId", "DatasetName" }
),
RefreshesAdded = Table.AddColumn(
DatasetFieldsExp,
"Refreshes",
each GetRefreshes([WorkspaceId], [DatasetId], [AccessToken])
),
RefreshesListExp = Table.ExpandListColumn(
RefreshesAdded,
"Refreshes"
),
RefreshFieldsExp = Table.ExpandRecordColumn(
RefreshesListExp,
"Refreshes",
{ "requestId", "refreshType", "startTime", "endTime", "status", "serviceExceptionJson" },
{ "RefreshId", "RefreshType", "StartTime", "EndTime", "Status", "ServiceExceptionJson" }
)
in
RefreshFieldsExp
The combined query creates a unified dataset containing workspace names, dataset names, refresh types, start times, end times, status indicators, and any service exception details.
Voilá!
Your Power BI Tracker now provides comprehensive monitoring capabilities across your entire Power BI environment. The resulting dashboard displays refresh status with intuitive color coding - green for successful refreshes, red for failures, and detailed error information for troubleshooting.
This solution transforms Power BI administration from reactive troubleshooting to proactive monitoring, enabling you to identify and resolve issues before they impact end users. The automated tracking eliminates manual checking routines and provides executive-level visibility into your organization's business intelligence infrastructure health.
