How to Trigger a Power BI Dataset Refresh via Azure Synapse Analytics in 3 Simple Steps.
In this blog post we’ll explore how to trigger a Power BI Dataset via Azure Synapse Analytics in 3 simple steps:
Step 1:
Create a new security group called “AzureSynapsePowerBIIntegration” in your Azure Active Directory.

Next add the name of your Azure Synapse Workspace as a member of your new security group. This will be your service principal.
Step 2:
Login to powerbi.com and in the top right hand corner locate “Settings” and then “Admin portal”
Under “Tenant settings” locate “Developer Settings” and then “Allow service principles to user Power BI APIs”.
Set this service to “Enabled” using the toggle. Next under the heading “Apply to:” select “Specific security groups (Recommended)”. Next add the newly created security group “AzureSynapsePowerBIIntegration” and click apply.

Now all that remains in the power bi service is to assign the security group “Member” workspace access to the dataset you want to refresh.

Step 3:
Login to your Azure Synapse Workspace.
Create a new pipeline called “Power BI Dataset Refresh” and then create a new web activity called “Refresh Dataset”.

Create two new parameters “WorkspaceGuidId” and “DatasetGuidId” both as strings.
Locate the WorkspaceGuidId (also known as the GroupId) and the DatasetGuidId from the URL of the dataset you want to refresh via powerbi.com
The URL will look like this below:
https://app.powerbi.com/groups/{groupId}/datasets/{datasetId}/details
Assign the Workspace/GroupId guid and the DatasetId guid to your newly created parameters.

Next within the settings of the “Web” activity paste the following dynamic content into the URL section.
@concat(‘https://api.powerbi.com/v1.0/myorg/groups/’,pipeline().parameters.WorkspaceGuidId,’/datasets/’,pipeline().parameters.DatasetGuidId,’/refreshes’)
And set the other options as per below:
Method:
POST
Body:
{}
Authentication:
System Assigned Managed Identity
Resource:
https://analysis.windows.net/powerbi/api

And that’s it…
All that is left to do now is debug / trigger the pipeline and see the dataset refresh in powerbi.com!

Some final points:
- If the dataset resides on a non premium capacity you can only schedule up to 8 refreshes per day.
- If the dataset resides on a premium capacity you can schedule up to 48 refreshes per day.
- Any datasets on a premium capacity don’t impose limitations for API refreshes.
- The API refresh data request only works when a published dataset is set to import mode rather than direct query.
- You can switch the method to a “GET” request instead and retrieve the refresh history of the dataset. The output of this could be saved as a variable and then subsequently written back to a database via a stored procedure.
Deploy Azure AutoML Model And Consume In Excel
It has come back to my turn to write a blog post, and if you remember my previous one concerned why you should use Azure based AutoMl and subsequently […] Continue Reading…
The SQL Query Alias Conundrum – Order of Execution
So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]’.
The column you’ve used in your WHERE clause cannot […] Continue Reading…
ADF Dataflow CTE workaround
At the time of writing, it is not possible to write a query using a CTE in the source of a dataflow. However, there are a few options to […] Continue Reading…
Download Power BI Reports using PowerShell
In this blog post I will be sharing a PowerShell script that allows multiple Power BI reports to be downloaded at once.
In the Power BI service, there is no […] Continue Reading…
How to parameterise the Execute Pipeline activity in Azure Synapse Analytics.
Unfortunately, as of April 2022 there is not an option to parameterise or add dynamic content to an “Execute Pipeline” activity to invoke a pipeline run.
However, with the use […] Continue Reading…
AutoML; How to use it and why
Introduction
Some of you may have noticed that the Purple Frog team were at the largest community-led SQL Server and Microsoft Data Platform conference in Europe last week; SQL Bits. With all […] Continue Reading…
Combining Queries from Multiple Sources in Power BI using Merge and Append
It is always good practice to do as much data preparation as close to the sources as you can before importing or connecting them to your Power BI reports, […] Continue Reading…
Execute SQL Task in ADF
If you’re new to ADF or Synapse pipelines and looking for a way to execute SQL commands in pipelines, unfortunately there is no equivalent to the Execute SQL Task […] Continue Reading…
Creating a quadratic solver in Python
In this blog post, I will be showing you how to create a python function that will solve any quadratic equation.
Firstly, what is a quadratic equation?
A quadratic is an […] Continue Reading…