How to refresh Power BI Dataflows with PowerShell

Reading Time: 3 minutes

CodeSnippets #4

With the introduction of Power BI Dataflows in April 2019 the ability to interrogate this new feature using the REST API landed soon after (unlike this blog post!). Like datasets, you can refresh your dataflows using the API.

I like to use my favourite scripting language to do this – PowerShell. Although we have the Power BI Management PowerShell module (MicrosoftPowerBIMgmt) to interact with Power BI, the cmdlets aren’t yet there to refresh or retrieve the history of a dataflow (or even a dataset) but the module can still help us get what we need without jumping through too many hoops (and as long as we aren’t automating the authentication, that’s another post.).

PowerShell

To initiate a dataflow refresh we need to call the Power BI REST API directly using PowerShell’s Invoke-RestMethod command. This means we need to provide it with the API url and an authentication header. The Power BI REST API documentation is great for getting endpoints, example outputs and parameters.

We can skip a complicated authentication process to obtain the required token by making use of a function that IS in the PowerShell module. Run the Install-module command to install the module from the PowerShell Gallery:

# Install Power BI Management module

Install-Module MicrosoftPowerBIMgmt

With the module installed, we login to Power BI and authenticate when prompted. We then run the Get-PowerBIAccessToken command to get the authentication token and build the API authentication header:

Login-PowerBI

$token = Get-PowerBIAccessToken

#Build the access token into the authentication header
$authHeader = @{
    'Content-Type'='application/json'
    'Authorization'= $token.Authorization
}

To call a refresh on a dataflow, we need it’s GUID and the GUID for the workspace it resides in.

We can source these from the address bar in Power BI or using PowerShell which is covered in the demo files of my Power BI and PowerShell presentation.

The final piece of the puzzle is setting the NotifyOption which allows us to alert on refresh failure, completion or nothing at all. Full info on usage is in the reference documentation. We’re now ready to call the API and initiate a dataflow refresh:

$uri = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.id)/Dataflows/$($dataflow.id)/refreshes"

# Build JSON, convert back and forth as we're just defining it as a string.
$json = '{"notifyOption": "MailOnFailure"}' | ConvertFrom-Json
$body = $json | ConvertTo-Json

Invoke-RestMethod -Uri $uri -Headers $authHeader -body $body -Method POST

It’s as simple as that and there’s no output unless you add the -Verbose flag at the end of the Invoke-RestMethod call, then you’ll see the actual API call.

The same process can be applied to datasets by simply changing the API url.

$uri = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.id)/Datasets/$($dataset.id)/refreshes"

# Build JSON, convert back and forth as we're just defining it as a string.
$json = '{"notifyOption": "MailOnFailure"}' | ConvertFrom-Json
$body = $json | ConvertTo-Json

Invoke-RestMethod -Uri $uri -Headers $authHeader -body $body -Method POST

We can also return info on the most recent 20 refreshes by simply change the API call from a POST to a GET and removing the -body parameter:

Invoke-RestMethod -Uri $uri -Headers $authHeader -Method GET

Which outputs a bit like this:

References

• Power BI Dataflows announcement
• Power BI API documentation
• Power BI Management documentation
• Article on PowerShell automation in Azure
  • Github repo for Power BI and PowerShell