Gain Visibility into Power BI Refresh Schedules with a Secure PowerShell Approach Improve Power BI Governance by Extracting Refresh Schedules via PowerShell
- Home
- Gain Visibility into Power BI Refresh Schedules with a Secure PowerShell Approach Improve Power BI Governance by Extracting Refresh Schedules via PowerShell
For Power BI administrators and enterprise BI governance teams, gaining full visibility into dataset refresh schedules across all workspaces remains a frustrating challenge. The Power BI Service lacks a native feature to export refresh configurations in bulk. That means admins are forced to manually inspect individual datasets—a time-consuming and error-prone process, especially in tenants with dozens or even hundreds of workspaces. As environments grow in scale and complexity, this gap quickly becomes a barrier to effective governance, compliance, and operational efficiency.
That’s where automation with PowerShell and Service Principal authentication comes in. In this blog, we’ll walk you through a robust PowerShell script that securely connects to your Power BI environment, iterates across all workspaces, and extracts rich metadata for every dataset—including connection modes, refresh enablement, schedule timing, and time zones. The result? A complete dataset refresh audit in minutes—fully automated, exportable, and ready for reporting or integration into your monitoring framework.
Objective: Export All Power BI Dataset Metadata and Refresh Schedules
In this, we’ll walk through a production-grade PowerShell script that:
This script is used by governance teams at Right Angle Solutions Inc. to automate visibility into Power BI tenant configurations—especially across large enterprise deployments.
How to Turn On Developer Settings
How to enable Service Principles to access Data
Prerequisites: What You’ll Need
To run this script, you need:
1) Azure App Registration (Service Principal)
Create an app in Azure AD and assign it these Power BI API permissions:
• Dataset.Read.All
• Workspace.Read.All
Ensure the Service Principal is enabled in the Power BI Admin portal.
2) Install Required Modules
Run this in your PowerShell terminal:
Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser
Import-Module MicrosoftPowerBIMgmt
3) Dependencies
What the Script Actually Does — Step-by-Step
The PowerShell Script Explained
Step 1: Setup Credentials -----
$clientId = "YOUR-CLIENT-ID"
This uses the Connect-PowerBIServiceAccount method for secure, non-interactive login.$clientSecret = "YOUR-CLIENT-SECRET"
$tenantId = "YOUR-TENANT-ID"
$secureClientSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $clientId, $secureClientSecret
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId $tenantId
Step 2: Loop Through Workspaces and Datasets -----
$workspaces = Get-PowerBIWorkspace -All
$results = @()
foreach ($workspace in $workspaces) {
Write-Host "Processing workspace: $($workspace.Name)"
$datasets = Get-PowerBIDataset -WorkspaceId $workspace.Id
foreach ($dataset in $datasets) {
Write-Host " -> Dataset: $($dataset.Name)"
We loop through every workspace and then every dataset inside it. At this point, we initialize metadata variables.
Step 3: Extract Metadata and Refresh Schedules -----
$connectionMode = "Unknown"$isRefreshable = $dataset.IsRefreshable
$configuredBy = $dataset.ConfiguredBy
$refreshEnabled = "No"
$refreshDays = ""
$refreshTimes = ""
$timeZone = ""
try
{$uriDataset = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($dataset.Id)"
$datasetDetails = Invoke-PowerBIRestMethod -Url $uriDataset -Method Get | ConvertFrom-Json
if ($datasetDetails.connectionDetails -ne $null)
{
$connectionMode = $datasetDetails.connectionDetails.mode}
}
catch {Write-Warning "Could not retrieve connection mode for dataset '$($dataset.Name)'"
}
if ($isRefreshable)
{try
{$uri = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($dataset.Id)/refreshSchedule"
$response = Invoke-PowerBIRestMethod -Url $uri -Method Get
$refreshSchedule = $response | ConvertFrom-Json
if ($refreshSchedule.enabled){$refreshEnabled = "Yes"
$refreshDays = ($refreshSchedule.days -join ", ")
$refreshTimes = ($refreshSchedule.times -join ", ")
$timeZone = $refreshSchedule.timeZone
}
}
catch {
Write-Warning "Skipping refresh schedule for dataset '$($dataset.Name)' in '$($workspace.Name)' – possibly not supported."
}
}
This section fetches connection mode (DirectQuery, Import, etc.) and refresh schedule settings, including timezone and configured days.
Step 4: Save the Metadata to a CSV -----
$results += [PSCustomObject]@{
WorkspaceName = $workspace.Name
DatasetName = $dataset.Name
DatasetId = $dataset.Id
ConnectionMode = $connectionMode
IsRefreshable = $isRefreshable
ConfiguredBy = $configuredBy
RefreshEnabled = $refreshEnabled
RefreshDays = $refreshDays
RefreshTimes = $refreshTimes
TimeZone = $timeZone
}
}
}
The PowerShell Script Explained
Powershell Output
Output Sample CSV File Name:
Use Cases for BI & Governance Teams
Manual checks are prone to errors and aren’t scalable. With this PowerShell script, you can export your entire Power BI refresh landscape in under 5 minutes.
RAS (Right Angle Solutions Inc.) uses this exact approach in large-scale enterprise Power BI environments to drive automation, governance, and operational efficiency.
Need Help with Power BI Automation?
Let RAS help you design and implement your Power BI governance and monitoring framework.
Contact us to schedule a consult.
Categories
Get in touch
Recent Posts
Archives