Scheduling Power BI Dataset Refresh using PowerShell

Refreshing datasets in Power BI ensures your reports always display the latest data. Automating dataset refresh using PowerShell helps maintain up-to-date insights without manual intervention. This guide walks you through scheduling a Power BI dataset refresh using PowerShell and Task Scheduler.

---

Step 1: Prerequisites

1. Power BI Requirements

• A 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 you haven’t already installed the Power BI PowerShell module, run:

Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser -Force

Now, you have Power BI PowerShell module installed!

---

Step 2: Authenticate to Power BI

Before scheduling a dataset refresh, you need to log in.

Connect-PowerBIServiceAccount

This command will prompt you to sign in with your Power BI account.

Authentication successful!

---

Step 3: Identify the Dataset and Workspace

You need to retrieve the Workspace ID and Dataset ID to trigger a refresh.

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: Trigger a Power BI Dataset Refresh

Once you have the Workspace ID and Dataset ID, trigger a dataset refresh.

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

# Trigger dataset refresh
Invoke-PowerBIRestMethod -Url "groups/$workspaceId/datasets/$datasetId/refreshes" -Method Post

Dataset refresh started!

---

Step 5: Automate Dataset Refresh Using Task Scheduler

1. Create a PowerShell Script

Save the following script as RefreshPowerBIDataset.ps1:

# Connect to Power BI
Connect-PowerBIServiceAccount

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

# Trigger dataset refresh
Invoke-PowerBIRestMethod -Url "groups/$workspaceId/datasets/$datasetId/refreshes" -Method Post

Write-Host "Dataset refresh triggered successfully!"

---

2. Schedule the Task in Windows Task Scheduler

1. Open Task Scheduler in Windows.
2. Click Create Basic Task.
3. Enter Task Name (e.g., “Power BI Dataset Refresh”).
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\RefreshPowerBIDataset.ps1"
9. Click Finish to schedule the refresh.

Now, your Power BI dataset refresh is fully automated!

---

Step 6: Verify the Dataset Refresh Status

To check if the dataset refresh was successful, run:

# Get refresh history
Get-PowerBIDataset -WorkspaceId $workspaceId | Where-Object { $_.Id -eq $datasetId } | Select-Object RefreshStatus

Example Output

RefreshStatus
-------------
Completed

Dataset refresh completed successfully!

---

Step 7: Handle Errors in Dataset Refresh

If the dataset refresh fails, check the error logs:

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

If you see an error, verify:

• Your dataset credentials are correct.
• Your Power BI license and permissions.
• The dataset isn’t in use during refresh.