Monitoring Power BI Dataset Refresh Status Using PowerShell

Monitoring Power BI dataset refresh status helps ensure your reports stay up to date without failures. Using PowerShell, you can track the status of dataset refreshes, troubleshoot failures, and automate notifications.

---

Step 1: Prerequisites

1. Power BI Requirements

• Power BI Pro or Premium Per User (PPU) license.
• Admin or Member access to the Power BI workspace containing the dataset.

2. Install Power BI PowerShell Module

If not installed, run:

Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser -Force

Now, Power BI PowerShell module is installed!

---

Step 2: Authenticate to Power BI

Run the following command to log in:

Connect-PowerBIServiceAccount

You will be prompted to sign in with your Power BI account.

Authentication successful!

---

Step 3: Identify the Dataset and Workspace

First, retrieve the Workspace ID and Dataset ID.

Get All Workspaces

Get-PowerBIWorkspace -Scope Organization | Select-Object Id, Name

Example Output

Id                                   Name
------------------------------------ -----------------------------------
abcd1234-5678-90ef-ghij-klmnopqrstuv Sales Reports
efgh5678-1234-ijkl-mnop-qrstuvwxyzab Marketing Analytics

Get Datasets in a Workspace

$workspaceId = "abcd1234-5678-90ef-ghij-klmnopqrstuv"
Get-PowerBIDataset -WorkspaceId $workspaceId | Select-Object Id, Name

Example Output

Id                                   Name
------------------------------------ -----------------
1234abcd-5678-ef90-ghij-klmnopqrstuv Sales Dataset
5678efgh-1234-ijkl-mnop-qrstuvwxyzab Marketing Dataset

Now, you have the Workspace ID and Dataset ID!

---

Step 4: Check Power BI Dataset Refresh Status

Now, retrieve the refresh status using PowerShell.

# Define variables
$workspaceId = "abcd1234-5678-90ef-ghij-klmnopqrstuv"
$datasetId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"

# Get the dataset refresh history
$refreshHistory = Invoke-PowerBIRestMethod -Url "groups/$workspaceId/datasets/$datasetId/refreshes" -Method Get

# Display refresh status
$refreshHistory.value | Select-Object requestId, startTime, endTime, status

Example Output

requestId                              startTime                endTime                  status
------------------------------------ ---------------------- ---------------------- ----------
abc1234-5678-ef90-ghij-klmnopqrstuv 2025-03-24T12:00:00Z  2025-03-24T12:05:00Z  Completed
xyz5678-1234-ijkl-mnop-qrstuvwxyzab 2025-03-24T11:00:00Z  2025-03-24T11:10:00Z  Failed

You can now track the refresh history!

---

Step 5: Monitor the Latest Dataset Refresh

To check the most recent dataset refresh:

# Get the latest refresh attempt
$latestRefresh = $refreshHistory.value | Sort-Object startTime -Descending | Select-Object -First 1

# Display latest refresh status
$latestRefresh | Select-Object requestId, startTime, endTime, status

Example Output

requestId                              startTime                endTime                  status
------------------------------------ ---------------------- ---------------------- ----------
abc1234-5678-ef90-ghij-klmnopqrstuv 2025-03-24T12:00:00Z  2025-03-24T12:05:00Z  Completed

The latest dataset refresh status is retrieved!

---

Step 6: Check Failed Refresh Attempts

If you want to list only failed refreshes, use:

# Get failed dataset refresh attempts
$failedRefreshes = $refreshHistory.value | Where-Object { $_.status -eq "Failed" }

# Display failed refreshes
$failedRefreshes | Select-Object requestId, startTime, endTime, status

Example Output

requestId                              startTime                endTime                  status
------------------------------------ ---------------------- ---------------------- ----------
xyz5678-1234-ijkl-mnop-qrstuvwxyzab 2025-03-24T11:00:00Z  2025-03-24T11:10:00Z  Failed

You can now track failed refreshes!

---

Step 7: Get Refresh Error Details

If a refresh failed, you can retrieve the error message:

# Get the latest failed refresh details
$latestFailedRefresh = $failedRefreshes | Sort-Object startTime -Descending | Select-Object -First 1

# Display error details
$latestFailedRefresh

If the output contains errorDetails, it provides the reason for failure.

Now, you can diagnose refresh failures!

---

Step 8: Automate Dataset Refresh Monitoring

To automate monitoring, save the following script as MonitorPowerBIRefresh.ps1:

# Connect to Power BI
Connect-PowerBIServiceAccount

# Define variables
$workspaceId = "abcd1234-5678-90ef-ghij-klmnopqrstuv"
$datasetId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"

# Get refresh history
$refreshHistory = Invoke-PowerBIRestMethod -Url "groups/$workspaceId/datasets/$datasetId/refreshes" -Method Get
$latestRefresh = $refreshHistory.value | Sort-Object startTime -Descending | Select-Object -First 1

# Check status and send notification
if ($latestRefresh.status -eq "Failed") {
    Write-Host " Dataset Refresh Failed: Check Power BI for details."
} else {
    Write-Host " Dataset Refresh Successful!"
}

Schedule the Script Using Task Scheduler

1. Open Task Scheduler
2. Click Create Basic Task
3. Enter Task Name: “Power BI Refresh Monitor”
4. Click Next, then select Daily (or another schedule)
5. Click Next, set the preferred time, and click Next
6. Select Start a Program and click Next
7. In the Program/Script field, enter: plaintextCopyEditpowershell.exe
8. In the Add arguments (optional) field, enter: -ExecutionPolicy Bypass -File "C:\Scripts\MonitorPowerBIRefresh.ps1"
9. Click Finish to schedule the task.

Now, Power BI refresh status is monitored automatically!