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.

What you need to know

There’s a few things you need to pay close attention to when setting the refresh schedule via the API:

• Unless you’re setting the refresh schedule for a Premium workspace, you can only refresh a dataset up to 8 times a day. We’re only going to set it to update once a day here, but keep this in mind if you’re planning to adjust the API call to refresh multiple times a day.
• The name of the time zone you provide has to match exactly with the names (middle column) in this reference: Microsoft Time Zone Index
• The refresh time has to be in the format hh:mm, and similar to the options in the Power BI portal you can only refresh on the hour or half-hour.

Initial steps

As we’ve done with all our PowerShell scripts so far, let’s create some parameters, connect to the service and retrieve the workspace and report objects:

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

We’re going to use the Patch method again here (see the previous post in the series for more info on that), and set the refresh schedule for once a day at the time (and time zone) provided in the parameters. Note that we’re also setting the notification option to MailOnFailure, which means that the owner of this dataset will get an email if the refresh fails.

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

$ApiRequestBody = @"
    {
        "value": {
            "days": [
                "Sunday",
                "Monday",
                "Tuesday",
                "Wednesday",
                "Thursday",
                "Friday",
                "Saturday"
            ],
            "times": [
                "$RefreshTime"
            ],
            "notifyOption": "MailOnFailure", 
            "localTimeZoneId": "$Timezone", 
            "enabled": true
        }
    }
"@ 


#Update refresh schedule
Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body ("$ApiRequestBody")

Write-Output "Data refresh schedule for report ""$ReportName"" updated successfully..." `n

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