Microsoft have just announced support for PowerShell in Azure functions runtime 2. The examples I have shown here earlier used the experimental support in runtime 1. This blogpost will show how to use Azure Functions runtime 2 with PowerShell and KeyVault to use the Power BI REST API.

Related posts:
Connecting Azure Functions to Key Vault
Refresh Power BI datasets with Data Factory

To use the Power BI API you must setup a app to talk to Power BI. This is documented here.

I recommend setting up the API for a user that have a password that won’t expire (or use service principals if you are using the new workspace experience). For this example I will use a user account, and put the Power BI ClientId, UserName and Password into KeyVault.

Next you can choose which APIs to support. I have chosen to have three functionalities; get a dataset and group id for a given group name and dataset name, refresh a dataset and get the refresh history for a dataset.

Find group and dataset id

Our first function will based on a group name and dataset name find the corresponding group id and dataset id. The full code for this in PowerShell is:

using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

#read groupName and datasetName from request
$groupName = $Request.Headers.groupName
$datasetName = $Request.Headers.datasetName

$authUrl = “https://login.windows.net/common/oauth2/token/”
$body = @{
“resource” = “https://analysis.windows.net/powerbi/api”;
“client_id” = $env:APPSETTING_PowerBIClientId;
“grant_type” = “password”;
“username” = $env:APPSETTING_PowerBIUserName;
“password” = $env:APPSETTING_PowerBIPassword;
“scope” = “openid”
}
$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body

# Building Rest API header with authorization token
$authHeader = @{
“Content-Type” = “application/json”;
“Authorization” = $authResponse.token_type + ” ” + $authResponse.access_token
}

#find the group – case sensitive search!
$groupUrl = “https://api.powerbi.com/v1.0/myorg/groups?`$filter=name%20eq%20’$groupName'”
$groups = Invoke-RestMethod -Uri $groupUrl –Method GET -Headers $authHeader
$groupId = $groups.value.id

#find dataset in group
$datasetsUrl = “https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets”
$datasets = Invoke-RestMethod -Uri $datasetsUrl –Method GET -Headers $authHeader
$datasets = $datasets.value

#loop so that we find the dataset
foreach($dataset in $datasets) {
if ($dataset.name -eq $datasetName) {
$datasetId = $dataset.id
break
}
}

$response = [HttpResponseContext]@{
StatusCode = ‘200’ # OK
ContentType = ‘application/json’
Body = @{ ‘groupId’ = $groupId; ‘datasetId’ = $datasetId }
}

#output
Push-OutputBinding -Name response -Value $response

PowerShell code for finding a groupId and datasetId

This reads the values from the header section. This is because when using GET in the Data Factory Azure Function Activity you will fill inn header values. Next the function gets an OAuth token before it finds the group id through a filter (case sensitive). Next it lists all datasets in that group and finds the one with correct name.

Finally it creates a response, and here I had to experiment a bit to get it to work properly with Data Factory. But this returns the correct group id and dataset id.

Refresh a dataset

Refreshing a dataset is easy when you have the group id and dataset id. You just need a different URL and will perform a post action (so you will use the body from data factory)

using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)


#read groupName and datasetName from request
$groupId = $Request.Body.groupId
$datasetId = $Request.Body.datasetId

#do the same authentication stuff as above

#get my groups
$restURL = “https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/refreshes”

$restResponse = Invoke-RestMethod -Uri $restURL –Method POST -Headers $authHeader
write-output “Refreshing report…”

Powershell refresh report

Get refresh history

Finally we have the last script for getting refresh history. This is more like the first script with a GET action:

using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

#read groupName and datasetName from request
$groupId = $Request.Headers.groupId
$datasetId = $Request.Headers.datasetId

#get token again…

$restURL = “https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/refreshes?`$top=1”

$restResponse = Invoke-RestMethod -Uri $restURL –Method GET -Headers $authHeader

$status = $restResponse.value.status
$startTime = $restResponse.value.startTime

$response = [HttpResponseContext]@{
StatusCode = ‘200’ # OK
ContentType = ‘application/json’
Body = @{ ‘status’ = $status; ‘startTime’ = $startTime }
}

#output
Push-OutputBinding -Name response -Value $response

Get refresh history with PowerShell – or at least get status and startTime

Now that you know all of this, it is time to head over to see how this can be used in Azure Data Factory!