As part of my work on Prism and other projects, managing Power BI refresh schedules at scale across multiple Power BI workspaces became essential. Manually setting schedules wasn’t scalable, and as Prism grew into a leading solution, automation became a necessity.
To solve this, I built a PowerShell script using the Power BI API. The script scans workspaces based on a defined description, applies a refresh schedule, and updates the dataset ID accordingly.
This script has now been used many times saving hours of manual work and hopefully it can help you too!
Why Automate?
Managing refresh schedules across multiple Power BI workspaces can quickly become time-consuming and inconsistent. Automating this process allows for simple bulk management and consistency.
How it works?
The script:
- Connects to the PowerBI service
- Retrieves all workspaces or filters with a specific name
- Searches for reports starting with “Aether” (modify as you need)
- Updates the refresh schedule to run daily at 16:00 UTC (modify time as you need)
https://github.com/AetherAdv/powerbi_powershell_updaterefresh
# Connect to Power BI Service Account
Connect-PowerBIServiceAccount
# Retrieve all workspaces
$workspaces = Get-PowerBIWorkspace -All
# Retrieve target workspace
#$workspaces = Get-PowerBIWorkspace -id "####"
# Loop through each workspace
foreach ($workspace in $workspaces) {
# Get reports with names starting with "AETHER" in the current workspace
$Reportlist = Get-PowerBIReport -WorkspaceId $workspace.Id | Where-Object { $_.Name -like 'AETHER*' }
# Check if any matching reports exist
if ($Reportlist) {
foreach ($Report in $Reportlist) {
# Ensure the report has an associated DatasetId (required for refresh scheduling)
if ($Report.DatasetId) {
$ReportName = $Report.Name
$WorkspaceId = $workspace.Id
$ReportId = $Report.Id
$DatasetId = $Report.DatasetId
Write-Host "Updating refresh schedule for: $ReportName in Workspace: $WorkspaceId"
# Construct the API URL for updating the refresh schedule
$ApiUrl = "https://api.powerbi.com/v1.0/myorg/groups/$WorkspaceId/datasets/$DatasetId/refreshSchedule"
# Define the refresh schedule settings
$ApiRequestBody = @{
value = @{
days = @("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") # Runs daily
times = @("16:00") # Refresh time in UTC
notifyOption = "MailOnFailure" # Notify only on failure
localTimeZoneId = "UTC" # Time zone for the refresh schedule
enabled = $true # Ensure the schedule is enabled
}
} | ConvertTo-Json -Depth 3 # Convert to JSON format
try {
# Send API request to update the refresh schedule
Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body $ApiRequestBody -Verbose
Write-Host "Successfully updated data refresh schedule for: $ReportName" -ForegroundColor Green
} catch {
# Handle any errors during the API request
Write-Host "Failed to update refresh schedule for: $ReportName" -ForegroundColor Red
Write-Host "Error: $_" -ForegroundColor Yellow
}
} else {
# Skip reports without a DatasetId (they cannot have a refresh schedule)
Write-Host "Skipping report '$ReportName' as it has no associated DatasetId." -ForegroundColor Yellow
}
}
}
}