Power BI Dataset Refresh

Introduction

A PowerShell script that will trigger a refresh on a Dataset in Power BI. The script will show ‘Completed’ and ‘Failed’ with error.

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.

Getting Started

Create the service principal

  1. Register an application using your Microsoft Azure account, https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade.
  2. Search for “App Registration” and create an app via “New registration”. registration
  3. Provide a name and click on Register.
  4. Take note of the ‘Client ID’ and ‘Tenant ID’. client&tenantID
  5. Navigate to Certificates & secrets in the left-hand menu and create a “New client secret”.
  6. Provide a description and change the expiry if needed, then click “Add”.
  7. Take note of the ‘Value’ of the Client secret. clientsecret
  8. Navigate to “API permissions” and click “Add a permission”.
  9. Search and choose “Power BI Service”.
  10. Click “Delegated permissions”.
  11. Search and tick the box for “Dataset.Read/Write.all”, and then click “Add permissions”. apipermissions

Please note that in case Admin consent is needed, click on “Grant admin consent”.

How to assign the App to a workspace in Power BI

  1. Go to Power BI and navigate to your Workspace.
  2. Click on Manage Access and add App (Service Principal) to the Power BI Workspace. workspaceaccess

This is the name of the App that you have created in App registration. The access level should be “Contributor”.

Setting up and running the Integrate task

  1. Create a task in Integrate with PowerShell Core as the task type.
  2. Add task parameters for the groupID and dataset with their values.

    You can get the values for groupID and dataset in the Power BI Dataset URL. https://app.powerbi.com/groups/(groupID)/datasets/(dataset)/details?experience=power-bi

    It is set to the task parameters so you can easily change/update the value without editing the script. groupanddataset

  3. Copy and paste the whole script. integratetask
  4. Change/update the variables in the script. Here are the variables that you need to update:
Variable Name Value
clientid You can get this from your App that you created in app registration
clientsecret You can get this from the your App registration via Certificates & secrets
tenantid You can get this from the your App registration

You will only need to update these variables in the script if you are using a different App from the App registration.

  1. Click “Submit” and run the task.

Copy the script below to paste into your Integrate PowerShell Core Task.


$clientid = "" # You can get this from the your App registration
$clientsecret = "" # You can get this from the your App registration via Certificates & secrets
$tenantid = "" # You can get this from the your App registration

#Obtaining Access Token
$scope = "https://analysis.windows.net/powerbi/api/.default"
$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 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

$statusURL = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$dataset/refreshes"
do {
    # Send the GET request to check the status
    $response = Invoke-RestMethod $statusURL -Method 'GET' -Headers $authHeader
    
    # Retrieve the status from the response
    $status = $response.value[0].status

    # Output the current status
    Write-Output "Current status: $status"

    # Wait for a few seconds before the next check
    Start-Sleep -Seconds 5
} while ($status -ne 'Completed' -and $status -ne 'Failed')

Write-Output "Status check complete. Final status: $status"

# Check if the refresh failed
if ($status -eq 'Failed') {
    # Retrieve the error message from the response
    $errorMessage = $response.value[0].serviceExceptionJson
    Write-Output "Failed to refresh the Dataset. $errorMessage"
}