The Power BI API contains several useful pieces of functionality if you need to do interact with Power BI at a lower API level. Power Automate provides great automation features, and it’s only natural we would want to use the two together. Power Automate does have a Power BI connector, but we can get right to the Power BI API to unlock more features as well. Let’s look at how we can use the Power BI API within Power Automate.
First, we will need to register a new Azure App for Power BI.
Once that’s done, let’s log into Power Automate at https://flow.microsoft.com and create a new Instant Flow, as a Manually triggered flow:
We start with a Manually trigger a flow. Let’s add another action, HTTP:
We will provide the following:
- Method = POST
- URI = https://login.microsoftonline.com/common/oauth2/token
- Headers: Content-Type application/x-www-form-urlencoded
- Body: Replace the bold below with your information:
grant_type=password
&username=your@email.com
&password=yourpassword
&client_id=your client id from the Azure app you create above
&client_secret=your secret from the Azure app you created above
&resource=https://analysis.windows.net/powerbi/api
After running this, we see we’re getting the output with our Bearer access token:
Now, let’s get this access token. Copy the Body from above and create a new Parse JSON action, then click Generate from Sample:
Paste this into the JSON Payload sample and click Done:
We see the schema has now been built for us:
We can now use the Bearer to connect to the Power BI API. Create another HTTP action:
We will perform a simple GET operation to get Power BI datasets. The URL for this is https://api.powerbi.com/v1.0/myorg/datasets. For the Headers, add:
- Authorization = Bearer <paste the access token from Dynamic content>
Now run the flow. We will test it manually:
We can see it ran successfully:
The output we get it datasets from Power BI. This was a simple example, you can also loop through the results, find multiple datasets, etc:
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
Thank you for putting this together!
Hi
Thanks for the article. I am facing a issue with this.
This works for me and only shows the dataset in the workspace of the singed in user.
I am trying to use this URL to get the dataset in another workspace.
https://api.powerbi.com/v1.0/myorg/groups//datasets
Do you know how to get this working? The user is Azure AD user so how can i give access on Power BI workspace to this Azure AD user as this does not come on the list when i try to add this user as a member.
Thanks
Naresh
How cheat the Power Bi Token?
Hello Carl,
very good article. Thank you very much.
Unfortunately I am getting an error while executing the first step (Authenticate to PowerBI). I provided the body with both my personal O365 email address (work account) and – as we use a 2 factor auth – a generated app password. When executing I get the following error. Do you have any idea about this issue?
{“error”:”invalid_grant”,”error_description”:”AADSTS50126: Error validating credentials due to invalid username or password.\r\nTrace ID: e9ecc032–b9e1bc986300\r\nCorrelation ID: d18c3772–d537a8948a37\r\nTimestamp: 2021-03-02 16:04:15Z”,”error_codes”:[50126],”timestamp”:”2021-03-02 16:04:15Z”,”trace_id”:”e9ecc032–b9e1bc986300″,”correlation_id”:”d18c3772–d537a8948a37″,”error_uri”:”https://login.microsoftonline.com/error?code=50126″}
Same error, did you figure it out?
i can not register for an app frompower bi service portal,since i am not an admin.Then how will I get the client ID?
Working Perfectly from my side, Thank you very much
Hello, Thank you for the article. I was able to generate the access token in Power Automate. When I perform the below step
We will perform a simple GET operation to get Power BI datasets. The URL for this is https://api.powerbi.com/v1.0/myorg/datasets. For the Headers, add:
Authorization = Bearer
I am getting this error
{
“statusCode”: 401,
“headers”: {
“Strict-Transport-Security”: “max-age=31536000; includeSubDomains”,
“X-Frame-Options”: “deny”,
“X-Content-Type-Options”: “nosniff”,
“Access-Control-Expose-Headers”: “RequestId”,
“RequestId”: “xxxxxxx”,
“Date”: “Sun, 21 Aug 2022 04:23:17 GMT”,
“Content-Length”: “0”
}
}
Could you please help me with this?
I had to click advanced options in the 2nd HTTP request and put my bearer token in the Value after selecting Raw. The Header did not work for me, it just said 403 error no matter what I did.