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.

At times you may want to refresh a Power BI dataset from outside the portal, either on-demand or as part of another process (think DevOps for instance). In those cases the API provides the ideal mechanism to do so. Just remember that you are still limited to 8 refreshes a day if you don’t have a Premium workspace, and using this method will not work beyond the number of allowed refreshes. You also cannot count on the API to return a useful error message in that case.

Initial steps

I’m sure the repetition is a little monotonous at this point, but setting up your PowerShell scripts with parameters and other steps to prepare for what you want to do is essential…and I’d rather repeat it again as opposed to forcing you to read the entire series. Here’s the code to add the required parameters, connect to the Power BI service and retrieve the necessary objects we’ll need for our API call:

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

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

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

#Connect to Power BI tenant
Connect-PowerBIServiceAccount | Out-Null 

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

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

The API call

The body of our refresh API request only has one property notifyOption. I prefer to use the MailOnCompletion option here, but you can also choose MailOnFailure or NoNotification to tweak the behavior.

Also note that we’re using the -Verbose option in the PowerShell cmdlet, which is useful if you want to return every message generated by the command. In my opinion this is a good place to use it, because you’re not going to get a ton of output and having the verbose output will help if you need to troubleshoot any errors.

#API url for the refresh
$ApiUrl = "groups/" + $WorkspaceObject.Id + "/datasets/" + $PbiReportObject.DatasetId + "/refreshes" 

$ApiRequestBody = @"
    {
        "notifyOption": "MailOnCompletion"
    }
"@ 


#Trigger refresh
Invoke-PowerBIRestMethod -Url $ApiUrl -Method Post -Body ("$ApiRequestBody") -Verbose

It’s worth pointing out that the notification email will be sent to the dataset owner and not the account that was used to trigger the refresh, but that’s pretty much all there is to it. Happy refreshing!

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