When you use scheduled refresh in the Power BI service, it pauses if nobody accesses the report or dataset for 60 days. That’s fine from a load perspective, but if you want your datasets to always have the latest data and be available on demand, you’ll need to stop that from happening.
Now there are some manual approaches such as creating a Subscription on each dataset within the service UI, but if we are putting our reports out at scale that just isn’t viable. So what we can do is build a PowerShell script that will loop through each of our datasets and prevent scheduled refresh from being disabled due to inactivity.
The best way to achieve this is to trigger activity on the dataset. I do this by querying a table and column I know exist and discarding the result.
For the full script, scroll down, but I’ll cover what it does and what bits you can change.
Configuration
When building the script, permissions can make it harder to directly access certain tables with PowerShell. If we know the exact name of the tables to try and a column within them then we access it directly.
Firstly within this part of the code, choose a table within your dataset. When I was building I was checking for a couple of tables that had the same column name.
#Select which tables exist in the datasets
$tablesToTry = @(
"######",
"######"
)
Next, pick a column to query. I used [id] in my example.
#Column to try within the tables
$columnSuffix = "####"
Lastly I had certain named datasets I wanted to query and some I wanted to exclude so that can be covered in this part of the code
#Dataset names to include or exclude - blank returns all, separate with | for multiple
$include = ""
$exclude = ""
How the script works
Connect to Power BI
The script starts by connecting to your Power BI account using:
Connect-PowerBIServiceAccount
If the connection fails, it stops and logs a warning. Once connected, the script can access all workspaces and datasets your account has permission to see.
Gather Workspaces and Datasets
It retrieves all your workspaces using:
Get-PowerBIWorkspace -All
Then, for each workspace, it lists all datasets. You can filter which datasets to include or exclude with simple patterns. For example:
$include = "Sales|Finance"
$exclude = "Test"
Test Tables and Columns
For each dataset, the script loops through a list of tables you specify:
$tablesToTry = @("Orders", "Customers")
$columnSuffix = "id"
It builds a DAX query for each table/column combination, using TOPN to return a small sample. This keeps the load low while confirming the table exists and is accessible.
Example query generated for the “Orders” table:
EVALUATE
TOPN(10, 'Orders', 'Orders'[id], DESC)
Execute Queries via REST API
The script runs the queries using the Power BI REST API:
Invoke-PowerBIRestMethod -Url "datasets/$($dataset.Id)/executeQueries" -Method Post
- If the query succeeds, it logs the dataset and table in
$successfulDatasets and prints the rows.
- If it fails, it captures the error and logs it in
$failedDatasets for troubleshooting later.
Trigger Report Usage
For each dataset, the script looks for reports linked to it:
Get-PowerBIReport -WorkspaceId $workspace.Id | Where-Object { $_.DatasetId -eq $dataset.Id }
If a report is linked, the script exports it to PDF and discards the file. The export counts as usage and keeps the dataset active.
Invoke-PowerBIRestMethod -Url "reports/$($report.Id)/ExportTo" -Method Post -Body (@{ format = "PDF" } | ConvertTo-Json)
Logging and Results
At the end, the script prints three tables:
- ✅ Successfully queried datasets
- ❌ Datasets that failed
- 📊 Datasets where usage was triggered
The Script
# ============================
# POWERBI Trigger Usage
# James Mounsey-Moran
# ============================
# ============================
# CONFIGURATION
# ============================
#Select which tables exist in the datasets
$tablesToTry = @(
"######",
"######"
)
#Column to try within the tables
$columnSuffix = "####"
#Dataset names to include or exclude - blank returns all, separate with | for multiple
$include = ""
$exclude = ""
# ============================
$daxQueryTemplate = @"
EVALUATE
TOPN(10, {0}, {1}, DESC)
"@
$failedDatasets = @()
$successfulDatasets = @()
$usageTriggered = @()
# ============================
# CONNECT TO POWER BI SERVICE
# ============================
try {
Connect-PowerBIServiceAccount -ErrorAction Stop
}
catch {
Write-Warning "Failed to connect to Power BI Service: $($_.Exception.Message)"
return
}
# ============================
# GET ALL WORKSPACES
# ============================
try {
$workspaces = Get-PowerBIWorkspace -All -ErrorAction Stop
}
catch {
Write-Warning "Failed to retrieve workspaces: $($_.Exception.Message)"
return
}
foreach ($workspace in $workspaces) {
Write-Output "`nProcessing workspace: $($workspace.Name) ($($workspace.Id))"
try {
$datasets = Get-PowerBIDataset -WorkspaceId $workspace.Id -ErrorAction Stop |
Where-Object {
(-not $include -or $_.Name -match "(?i)$include") -and
(-not $exclude -or $_.Name -notmatch "(?i)$exclude")
}
}
catch {
Write-Warning "Failed to list datasets in workspace $($workspace.Name): $($_.Exception.Message)"
continue
}
if ($datasets.Count -eq 0) {
Write-Output " No datasets found. Skipping."
continue
}
foreach ($dataset in $datasets) {
Write-Output " Processing dataset: $($dataset.Name) ($($dataset.Id))"
$queriedSuccessfully = $false
$tableErrors = @()
foreach ($tableName in $tablesToTry) {
$quotedTable = "'$tableName'"
$columnName = "$quotedTable[$columnSuffix]"
$daxQuery = [string]::Format($daxQueryTemplate, $quotedTable, $columnName)
Write-Output " Trying DAX: $daxQuery"
try {
$response = Invoke-PowerBIRestMethod `
-Url "datasets/$($dataset.Id)/executeQueries" `
-Method Post `
-Body (@{
queries = @(@{ query = $daxQuery })
serializerSettings = @{ includeNulls = $true }
} | ConvertTo-Json -Depth 5) `
-ErrorAction Stop
$json = $response | ConvertFrom-Json
Write-Output " ✅ Query successful for table $tableName."
$json.results[0].tables[0].rows | Format-Table
$queriedSuccessfully = $true
$successfulDatasets += [PSCustomObject]@{
Workspace = $workspace.Name
Dataset = $dataset.Name
DatasetId = $dataset.Id
Table = $tableName
Timestamp = Get-Date
}
break
}
catch {
$fullMsg = $_.Exception.ToString()
if ($fullMsg -match '"value":\s*"([^"]+)"') {
$errorMessage = $matches[1]
}
else {
$errorMessage = $_.Exception.Message
}
Write-Warning " ❌ Failed for table $tableName $errorMessage"
$tableErrors += "Table $tableName $errorMessage"
}
}
if (-not $queriedSuccessfully) {
Write-Warning " Could not query dataset $($dataset.Name) with any table."
$failedDatasets += [PSCustomObject]@{
Workspace = $workspace.Name
Dataset = $dataset.Name
DatasetId = $dataset.Id
Error = ($tableErrors -join "; ")
Timestamp = Get-Date
}
}
# ============================
# REGISTER USAGE (EXPORT REPORT)
# ============================
try {
$reports = Get-PowerBIReport -WorkspaceId $workspace.Id | Where-Object { $_.DatasetId -eq $dataset.Id }
if ($reports.Count -gt 0) {
$report = $reports[0] # just pick the first linked report
Write-Output " Triggering usage via ExportTo on report: $($report.Name) ($($report.Id))"
$null = Invoke-PowerBIRestMethod `
-Url "reports/$($report.Id)/ExportTo" `
-Method Post `
-Body (@{ format = "PDF" } | ConvertTo-Json -Depth 3) `
-ErrorAction Stop
Write-Output " ✅ Usage registered for dataset $($dataset.Name) via report $($report.Name)."
$usageTriggered += [PSCustomObject]@{
Workspace = $workspace.Name
Dataset = $dataset.Name
Report = $report.Name
Timestamp = Get-Date
}
}
else {
Write-Warning " ⚠️ No report found linked to dataset $($dataset.Name). Cannot register usage."
}
}
catch {
Write-Warning " ❌ Failed to trigger usage for dataset $($dataset.Name): $($_.Exception.Message)"
}
}
}
# ============================
# REPORT SUCCESSFUL DATASETS
# ============================
if ($successfulDatasets.Count -gt 0) {
Write-Output "`n✅ Successfully queried datasets:"
$successfulDatasets | Format-Table -AutoSize
}
# ============================
# REPORT FAILED DATASETS
# ============================
if ($failedDatasets.Count -gt 0) {
Write-Output "`n❌ Datasets failed to query:"
$failedDatasets | Format-Table -AutoSize
}
# ============================
# REPORT USAGE REGISTRATION
# ============================
if ($usageTriggered.Count -gt 0) {
Write-Output "`n📊 Usage registered for datasets:"
$usageTriggered | Format-Table -AutoSize
}
else {
Write-Output "`n⚠️ No usage registrations were made."
}
Once this is running, you won’t need to worry about refresh being paused after 60 days. The script keeps everything ticking over by simulating activity, so your datasets stay active and ready whenever you need them.
