Automate Power BI Dataset Refresh using PowerShell

Introduction

Power BI allows you to schedule a Dataset refresh through the power BI portal (https://app.powerbi.com).

If you expect the dataset to refresh as soon as the new records are inserted in the underlying tables then you do not want the ability to trigger a dataset refresh from another source, this blog will help you with how to do that. We are using Powershell to do the same.

Pre-requisites

• Datasets in Power BI Premium, Premium per user, and Power BI Embedded

• GroupId and DatasetId are required

• The required permission scope is Dataset.ReadWrite.All

The number of refreshes is limited to the general limitations of API-based refreshes for both Pro and Premium datasets.

Register an application

Go to https://portal.azure.com and sign in using your Office 365 account. Navigate to App Registration and create a new app by providing a name.

Note down the Client ID and Tenant ID; this will be useful in the later steps. Then, create a client secret and note it down

Provide API Permissions by choosing Power BI and choose “Read and write all datasets”

Add Service Principal to Power BI Workspace

Add the newly created Server Principal to the Power BI workspace.

Note that API access is subject to your Power BI subscription. It is recommended that you use a Power BI Premium subscription for the same.

PowerShell Script

Set Variables

You will need the following parameters for this PowerShell script

• Tenant ID - Obtained during the app creation step

• Client ID - Obtained during the app creation step

• Client secret - Obtained during the app creation step

• Group ID - Screenshot below

• Dataset ID - Screenshot below

Set the PowerShell variables

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$clientid = "7XXXXXXXXXXXXXXXXXXXXXXXXa4c9" 
$clientsecret = "XbQXXXXXXXXXXXXXXXXXXXMIaPo"
$scope = "https://analysis.windows.net/powerbi/api/.default"
$tenantid = "06dXXXXXXXXXXXXXXXXXXXXXXXXXXXd75"
$groupID = “XXXXXXXXXXXXXXXXXXX”
$dataset = “ZSER54XXXXX”

Obtain Access Token

The code below will return the access token.

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/x-www-form-urlencoded")
$body = "client_id=$clientid&client_secret=$clientsecret&scope=$scope&grant_type=client_credentials"

$response = Invoke-RestMethod "https://login.microsoftonline.com/$tenantid/oauth2/v2.0/token" -Method 'POST' -Headers  $headers -Body $body

$token = $response.access_token
$token = "Bearer $token"

Refresh the dataset

Below block of code will trigger the Dataset

$authHeader = @{
      'Content-Type'='application/json'
       'Authorization'= $token
      }
$groupsPath = ""
if ($groupID -eq "me") {
      $groupsPath = "myorg"
     } else {
        $groupsPath = "myorg/groups/$groupID"
  }
 $uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$dataset/refreshes"
 Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST

Get the status of Dataset

This Code helps to return the status of Dataset

$statusURL = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$dataset/refreshes"
$response = Invoke-RestMethod $statusURL -Method 'GET' -Headers $authHeader
$status = $response.value[0].status

Testing

The final step is to run the PowerShell script, ensure that it runs, and dataset gets refreshed.

Result

You can run the script in Powershell

You can check the dataset refresh history to ensure a successful refresh.

GitHub Link