Refresh Power BI Datasets with PowerShell

This post contains a PowerShell script that triggers a refresh process for three Power BI datasets. Additionally, this script is called/executed as part of automated processes via SQL Server Agent and SQL Server Integration Services (SSIS).

Given the length of the previous post regarding group email notification, as well as the option to schedule a refresh in the Power BI service, I intentionally left out details regarding dataset refresh for the report that’s driving the notification. In many cases scheduled refresh in the Power BI service may be sufficient but ideally you’d want to synchronize the data retrieval (the JSON files with refresh history) with the dataset refresh to immediately deliver the notification once the new data is available.

The Datasets to Refresh

In this example, the objective is to trigger the refresh of the following three datasets in an app workspace named “BI Administration and Monitoring”:

Power BI Datasets to Refresh

As an aside, if you’re responsible for administering Power BI in your organization or part of a team that shares these duties you may consider a dedicated app workspace to host various monitoring and admin solutions. For example, you could include Azure Active Directory group data, Analysis Services metadata and performance metrics (if applicable), in addition to core Power BI usage analytics, on-premises data gateway metrics, and more.

Dataset Refresh Script

The PowerShell script calls the Refresh Dataset in Group Power BI REST API via the Invoke-PowerBIRestMethod cmdlet, which is included in the Power BI Management module. In the following example, the user account of a Power BI Service Administrator is used for authentication. Alternatively, a service principal could also be used per the documentation on the Connect-PowerBIServiceAccount cmdlet.

Both the sample script below and a parameterized sample script (for use in SSIS example) have been uploaded to GitHub:

#1. Authenticate to Power BI with Power BI Service Admin account

$User = ""
$PW = "theuserpw"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

#2. Get the workspace ID of the dataset(s) to be refreshed

$WSIDAdmin = Get-PowerBIWorkspace -Scope Organization -Name 'BI Administration and Monitoring' | `
    Where {$_.Type -eq "Workspace"} | ForEach {$_.Id}

#3. Get the dataset ID(s) of the datasets to be refreshed 

#Refresh History dataset
$DSIDRefresh = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | ` 
    Where {$_.Name -eq "Refresh History"} | ForEach {$_.Id}

#PBI Tenant Wide Monitoring dataset
$DSIDMonitor = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | ` 
    Where {$_.Name -eq "PBI Tenant-Wide Deployment"} | ForEach {$_.Id}

#Gateway Monitoring dataset
$DSIDGateway = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | ` 
    Where {$_.Name -eq "Gateway Analysis"} | ForEach {$_.Id}

#4. Build dataset refresh URLs

$RefreshDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDRefresh + '/refreshes'

$MonitorDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDMonitor + '/refreshes'

$GatewayDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDGateway + '/refreshes'

#5. Execute refreshes with mail on failure

$MailFailureNotify = @{"notifyOption"="MailOnFailure"}

Invoke-PowerBIRestMethod -Url $RefreshDSURL -Method Post -Body $MailFailureNotify

Invoke-PowerBIRestMethod -Url $MonitorDSURL -Method Post -Body $MailFailureNotify

Invoke-PowerBIRestMethod -Url $GatewayDSURL -Method Post -Body $MailFailureNotify

Per the comments in the script above, following authentication to the Power BI service, the workspace ID and dataset IDs are retrieved via standard Get cmdlets included in the Power BI Management module. Once these values (GUIDs) are obtained and stored in variables, separate URL strings are built for each dataset to be refreshed and these custom strings are also assigned to variables. Finally, the REST API to trigger the refresh is called by passing the respective URL variable for the given dataset.

Script Notes

  • This pattern of retrieving the IDs for workspaces, datasets, and other Power BI artifacts (reports, dashboards) and then passing the values to various other cmdlets and expressions is very common.
  • The Where-Object cmdlet for type = “Workspace” is only necessary to avoid a scenario in which a workspace with the same name was created with the current Office 365 group-based workspace experience.
    • At some point later this year following GA of the new Power BI workspaces the current/legacy Office 365 group based workspaces will be migrated to the new workspace experience.
  • You ‘could’ just navigate to the workspace(s) and dataset(s) in the Power BI service to obtain the IDs necessary for the script rather than retrieve the values on the fly but of course this approach would not be resilient to change.
  • The refresh will still execute if you don’t supply a value for the Body parameter – you’ll just get a warning since this is a POST method (not a GET).

Dataset Refresh Automation

The dataset refresh script is only useful when it’s called from an automation/orchestration tool as part of a process impacting the data ingested by the Power BI dataset(s). Probably in the most common scenario you want the Power BI dataset(s) to be refreshed immediately following an update to the source system such as a data warehouse or reporting data marts.

From a SQL Server Agent perspective, you could create a dedicated job with a PowerShell step and call this job from the sp_start_job stored procedure. Alternatively, you could just add a Powershell step to an existing Agent job such as the following example for refresh history data:

Steps Page of SQL Agent Job Properties

In the example above, the Refresh History dataset is refreshed immediately following the successful execution of a PowerShell script that writes refresh history data to JSON (to be retrieved by the dataset).

The PowerShell step type in SQL Server Agent may be sufficient in some cases but it doesn’t allow for abstracting away sensitive values such as the credential used for authentication. In this case, and particularly if SSIS packages are already being used, one option is to re-factor the PowerShell script to accept parameters from a calling application and then leverage the project or package-scoped parameters in SSIS like the following example:

Expression Builder for Execute Process Task in SSIS

In the image above, an SSIS expression is created for the Arguments property of the Execute Process Task Editor. The values for three package-scoped parameters (script, user, pw) are passed into this expression to execute the refresh. As parameters in SSIS, the admin team more easily manage and secure the values.

The Execute Process Task could also be used to just execute the PowerShell script (without the custom expression and parameters) per the following blog post.

See the parameterized script on GitHub as well as a previous post that highlighted executing PowerShell from SSIS for additional details.

Coming to California

I’ll be delivering a one hour session on custom Power BI Administration solutions on April 13th at SQL Saturday 847 Orange County. If you’re around the Los Angeles area and interested in this topic or anything else I write or blog about it would be a pleasure to meet you.


  1. Nice post! What about taking it one step further and orchestrating / automatically trigger of EDW > Power BI Dataflows (REST API is available) and than triggering the associated Power BI datasets after that? less SQL queries to EDW and faster refresh of datasets (because it will load data directly from Power BI Dataflows to datasets). After that there is also no need to setup a refresh schedular for dataflows/datasets.


    1. Thanks Michiel –

      Yes, given availability of dataflows REST API and use cases for dataflows generally I should probably write a follow up post that describes orchestrating this two-step process – fefresh one or multiple dataflows and then refresh one or multiple datasets which use the dataflows as their source.



  2. Hi Brett,

    Great article and very easy to follow!
    The data sets and URL’s are hard coded in this script but i was wondering if it is possible to dynamically build these.
    In this case, the PowerBI admin needs to be aware of all the data sets in the workspace. If a new data set is added, the powershell script needs to be updated.
    I was wondering if there is a way to dynamically get the list of all the data sets in a given workspace and build/execute the refreshes.



  3. Hi Brett!

    This is an amazing article for us to go through and execute however, I wanted a specific help around the refreshing the dataset URL. I have tried above but not able to trigger refresh in the services.

    Can you please help me here.



  4. Hi Brett,

    thanks a ot for the script. It worked first try with my user. But when I use a technical user (with e-mail and Power BI Pro license) I get an error “Get-PowerBIWorkspace : Operation returned an invalid status code ‘Unauthorized'”.

    The technical user is Admin on the workspace.
    It only works when I add the user as a “Power BI Admin” in the O365 Admin portal.
    Is that the correct way?



    1. Hi Daniel,

      You can execute a data refresh from the context of user account that is not a Power BI Admin but you’ll have to use slightly different syntax.

      #Get the workspace ID (from non-admin account):

      $WSID = (Get-PowerBIWorkspace -Name “The Workspace Name” -Scope Individual).Id

      #Notice that the scope parameter is Individual and we’re not specifying the Status or Type properties in a Where-Object since these properties are not available for non-admin accounts.

      Reading the workspace ID into a variable is the only part of the script that needs to change. I tested this out from a user that has the Contributor role to a workspace. Power BI Pro or Pro trial license is required for workspace admins, members, and contributors.

      Hope this helps. I’ll look to update this blog post fairly soon with more details.


    2. I confirmed earlier today that a user who is not a power bi admin can refresh datasets provided that user has permissions to the workspace and it’s datasets. I can’t provide any more detail.


      1. Hi Brett,

        it works! Thanks a lot, I really appreciate your supprt. I needed to change the scope not only during the Workspace lookup but also during the dataset lookup and the script now looks like this:

        #1. Authenticate to Power BI with Power BI Service Admin account

        $User = “”
        $PW = “pwd

        $SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force
        $myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

        Connect-PowerBIServiceAccount -Credential $myCred

        #2. Get the workspace ID of the dataset(s) to be refreshed

        $WSID = (Get-PowerBIWorkspace -Name “Workspace Name” -Scope Individual).Id

        #3. Get the dataset ID(s) of the datasets to be refreshed

        #Refresh History dataset
        $DSIDRefresh = Get-PowerBIDataset -Scope Individual -WorkspaceId $WSID | `
        Where {$_.Name -eq “Data Set Name”} | ForEach {$_.Id}

        #4. Build dataset refresh URLs

        $RefreshDSURL = ‘groups/’ + $WSID + ‘/datasets/’ + $DSIDRefresh + ‘/refreshes’

        #5. Execute refreshes with mail on failure

        $MailFailureNotify = @{“notifyOption”=”MailOnFailure”}

        Invoke-PowerBIRestMethod -Url $RefreshDSURL -Method Post -Body $MailFailureNotify

        Have a nice weekend


  5. Hi Brett, This is a nice article. I need some info related to Powershell script. I am able to get the recent refresh status by using the Powershell script with GET method. My ask is from the latest status of the refresh I need to get only the status field value from JASON output and as well as I need to keep on checking the status of my refresh until it completes (success or failure). Any suggestion/sample with respect to this solution would be apprciatable.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s