Power BI Dataset Refresh Failure Report

<#
.SYNOPSIS
    Reports on Power BI dataset refresh history across workspaces, flagging failures and long running refreshes.
.DESCRIPTION
    Connects to the Power BI service and loops through every workspace the signed in account can see, or a specific
    list you pass in, pulling the recent refresh history for each dataset. Datasets with a failed refresh in the
    lookback window, or a refresh that ran past a duration threshold, are flagged separately in the output and in an
    optional CSV export, so you can catch problems before someone opens a stale report and asks why the numbers
    look wrong.
.PARAMETER WorkspaceName
    One or more workspace names to check. If you leave this out, the script checks every workspace the signed in
    account has access to.
.PARAMETER DaysBack
    How many days of refresh history to pull per dataset. Defaults to 7.
.PARAMETER LongRunningMinutes
    A refresh that takes longer than this many minutes is flagged as long running even if it succeeded. Defaults to 30.
.PARAMETER ExportPath
    Optional path to a CSV file. When supplied, the full result set is exported there in addition to the console output.
.EXAMPLE
    .\Get-PowerBIDatasetRefreshReport.ps1 -DaysBack 14 -ExportPath C:\Reports\PBIRefreshes.csv

    Checks the last 14 days of refresh history across all reachable workspaces and writes the results to a CSV file.
.EXAMPLE
    .\Get-PowerBIDatasetRefreshReport.ps1 -WorkspaceName "Finance", "Ops Reporting" -LongRunningMinutes 20
.AUTHOR
    Shehryar Hassan
#>

[CmdletBinding()]
param(
    [string[]]$WorkspaceName,
    [int]$DaysBack = 7,
    [int]$LongRunningMinutes = 30,
    [string]$ExportPath
)

if (-not (Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt)) {
    Write-Error "The MicrosoftPowerBIMgmt module is not installed. Run: Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser"
    return
}

Import-Module MicrosoftPowerBIMgmt -ErrorAction Stop

try {
    Get-PowerBIAccessToken -ErrorAction Stop | Out-Null
}
catch {
    Write-Host "Connecting to the Power BI service, sign in when prompted."
    Connect-PowerBIServiceAccount | Out-Null
}

if ($WorkspaceName) {
    $workspaces = foreach ($name in $WorkspaceName) {
        Get-PowerBIWorkspace -Name $name -Scope Organization
    }
}
else {
    $workspaces = Get-PowerBIWorkspace -Scope Organization -All | Where-Object { $_.Type -eq "Workspace" -and -not $_.IsOrphaned }
}

if (-not $workspaces) {
    Write-Warning "No matching workspaces found."
    return
}

$cutoff = (Get-Date).AddDays(-$DaysBack)
$results = New-Object System.Collections.Generic.List[Object]

foreach ($workspace in $workspaces) {
    $datasets = Get-PowerBIDataset -WorkspaceId $workspace.Id

    foreach ($dataset in $datasets) {
        if (-not $dataset.IsRefreshable) {
            continue
        }

        try {
            $history = Get-PowerBIDatasetRefreshHistory -WorkspaceId $workspace.Id -DatasetId $dataset.Id |
                Where-Object { [datetime]$_.StartTime -ge $cutoff }
        }
        catch {
            Write-Warning "Could not read refresh history for '$($dataset.Name)' in '$($workspace.Name)': $($_.Exception.Message)"
            continue
        }

        foreach ($refresh in $history) {
            $start = [datetime]$refresh.StartTime
            $end = if ($refresh.EndTime) { [datetime]$refresh.EndTime } else { $null }
            $durationMinutes = if ($end) { [math]::Round(($end - $start).TotalMinutes, 1) } else { $null }

            $flag = if ($refresh.Status -eq "Failed") {
                "Failed"
            }
            elseif ($durationMinutes -and $durationMinutes -gt $LongRunningMinutes) {
                "Long running"
            }
            else {
                "OK"
            }

            $results.Add([PSCustomObject]@{
                Workspace       = $workspace.Name
                Dataset         = $dataset.Name
                Status          = $refresh.Status
                StartTime       = $start
                DurationMinutes = $durationMinutes
                RefreshType     = $refresh.RefreshType
                Flag            = $flag
                ErrorMessage    = $refresh.ServiceExceptionJson
            })
        }
    }
}

if (-not $results.Count) {
    Write-Host "No refresh history found in the last $DaysBack day(s)."
    return
}

$flagged = $results | Where-Object { $_.Flag -ne "OK" }

Write-Host ""
Write-Host "Checked $($results.Count) refresh(es) across $($workspaces.Count) workspace(s)."
Write-Host "$($flagged.Count) refresh(es) flagged as failed or long running."
Write-Host ""

$results | Sort-Object Workspace, Dataset, StartTime -Descending | Format-Table Workspace, Dataset, Status, StartTime, DurationMinutes, Flag -AutoSize

if ($ExportPath) {
    $results | Export-Csv -Path $ExportPath -NoTypeInformation
    Write-Host "Full results exported to $ExportPath"
}