Martin's Blog

This blog post is part of my “Automating Power BI deployments” series, and you can find a list of the other posts here.

The fun starts here!

We’ve only used the PowerShell cmdlets for Power BI so far in this series, but things are about to get interesting because there aren’t cmdlets available for everything you might want to do. One such thing is updating parameters, and we’re going to use the Power BI REST API (which the cmdlets use underneath the covers anyways) to achieve that.

REST APIs are usually a little tricky to deal with, especially the process of authentication. Fortunately there is an Invoke-PowerBIRestMethod cmdlet that makes it possible to use the API in PowerShell without the need to deal with some of the underlying complexities.

Setting the scene

For the purposes of this blog post, let’s assume you have a report that you’d like to customize for different customers and deploy to their workspaces. You define a parameter (Customer Name) in your data model and use the parameter to filter incoming data and/or change some labels on your report.

To get started we’ll define the following parameters as input to our PowerShell script:

param 
(    
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()] 
    [String]
    $WorkspaceName, 

    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()] 
    [String]
    $ReportName, 

    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()] 
    [String]
    $CustomerName
)

Before we can use the API to update the Customer Name parameter we will need to get the workspace, report and dataset id’s of our report. I prefer to use the names as input parameters to the script because it’s easier than remembering id’s, and then do the work of getting the actual objects in the script because that allows me to access all the attributes that PowerShell exposes through the cmdlet. This is what the initial assignments will look like:

#Retrieve the workspace
$WorkspaceObject = (Get-PowerBIWorkspace -Scope Organization -Name $WorkspaceName)

#Retrieve the report
$PbiReportObject = (Get-PowerBIReport -Workspace $WorkspaceObject -Name $ReportName)

The API call

Each API call consists of three main components:

• The method (Post, Get, etc.) which defines what type of work the API is going to perform. We’ll need to do a Post when updating the parameter.
• The URL that navigates to the relevant workspace, report or dataset as well as define what function we’re going to call (i.e what we’re trying to do).
• The request body that contains the details.

The URL

To update parameters, we’re going to use the UpdateParameters operation which takes the following form:

https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/Default.UpdateParameters

The request body

The request body contains all of the required info to update the parameter(s), and the syntax looks like this:

#Build the request body
$ApiRequestBody = @" 
{
    "updateDetails": [        
        {
             "name": "Customer Name", "newValue": "$CustomerName"
        }
    ]
}
"@

PowerShell makes it easy to interject our parameter $CustomerName into the text of the body as you can see from the snippet above. Before we wrap this up with the actual API call, there’s a few things I need to point out:

• We’re making use of a multi-line string to build the body of the API request, denoted by @” at the start and “@ at the end. The catch here is that the closing “@ has to be the first characters on the line without any preceding characters (including spaces). This will look a little odd if you’ve used indentation to make your code more readable, but required to make it work.
• The API is very touchy-feely about syntax, so pay attention to capitalization, spacing etc.
• Error messages returned by the API aren’t very descriptive and troubleshooting will be painful if you don’t get the request body one hundred percent correct.
• Be careful when copying and pasting the code from Microsoft’s official API documentation. I’ve had a few instances where some special characters (hidden to the naked eye) caused havoc and took forever to identify as the root cause for the failures.

Invoke the REST API method

The rest if pretty straight-forward, and we invoke the API call with the following cmdlet:

#Update parameters 
Invoke-PowerBIRestMethod -Url $ApiUrl -Method Post -Body ("$ApiRequestBody")

Want to download the PowerShell scripts to perform these actions? Get it from my GitHub repo.