Playing around with Microsoft Flow recently, I was reminded of the following blog post from a few months ago by Konstantinos Ioannou about using Flow to call the Power BI REST API to refresh a dataset:
https://medium.com/@Konstantinos_Ioannou/refresh-powerbi-dataset-with-microsoft-flow-73836c727c33
I was impressed by this post when I read it, but don’t think I understood quite how many exciting possibilities this technique opens up for Power BI users until I started to use it myself. The Power BI dev team are making a big investment in the API yet most Power BI users, myself included, are not developers and can’t easily write code (or PowerShell scripts) to call the API. With Flow, however, you can use the API without writing any code at all and solve a whole series of common problems easily. In this series of blog posts I’m going to show a few examples of this.
Before that, though, there is something to add to what Konstantinos says about creating the custom connector. In his post he describes how you can add individual actions to the custom connector, but with Flow you can also create a custom connector from an OpenAPI definition and it turns out that there is an OpenAPI definition file for the Power BI REST API available here. As a result you can create a Flow custom connector with (at the time of writing) a whopping 116 actions by following the instructions in Konstantinos’s post, but instead of using the “Create from blank” option using the “Import an OpenAPI file” option instead and uploading the Power BI REST API OpenAPI definition file. You’ll find that the text for the summaries and descriptions of many of the actions needs cleaning up – in particular, Flow doesn’t allow actions with names that finish with a dot so you’ll have to go through each one and remove that character – but doing this is much faster and easier than adding the actions manually.
And one last thing to point out: as Konstantinos mentions in his post, if you get a 404 error while testing the connector, just wait a few minutes!
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
Chris, you mention: “it turns out that there is an OpenAPI definition file for the Power BI REST API available here” which points to https://docs.microsoft.com/en-us/connectors/custom-connectors/define-openapi-definition . While this is an excellent description of the OpenAPI capabilities, they use Text Analytics as an example. I was expecting json file for PowerBI somewhere, but can’t find it at that page. Any idea?
Sorry, wrong link. I’ll fix it now…
This is a great post, thanks a lot! The link now opens a github page where we can switch to “Raw” and copy&paste the json in an new file that i can upload. However, when i upload the swagger file, I get an exclamation mark within a triangle in front of all the actions an references on the “Definitions” pane. Validation errors are “Contains a schema which has properties yet is not marked as type ‘object’.” and “Type not defined”. While I can define the type by setting it to string, I still have the first error. What am I missing here?
Don’t worry – these errors don’t stop the connector working.
Hi Chris – unable to find the OpenAPI definition file for the Power BI REST API at the link behind “here”… was that a mistake?
doh! looks like Rene beat me to it 😉
I fixed the link about 30 mins ago – are you looking at the post in a browser?
This looks very helpful – thanks for sharing.
Chris, although following the instructions carefully I could not create a custom connector.
“Encountered internal server error from Azure Resource Manager.
The tracking Id is ‘56297c7d-7f87-415c-b2ba-9b1c5cafa3d6’.”
Trying the “Create from blank” option just gives me another tracking Id.
I don’t know what to do with that Tracking Id so any help is appreciated.
I just created a custom connector from the OpenAPI definition and was able to successfully test many of the actions. Curious though, on the action “Creates new content on My Workspace from pbix, Excel, or file in OneDrive”, what do I use in the importInfo field? I’m trying to import an Excel workbook from OneDrive, and I’ve tried using the Get File Content action first, and then using File Content in the importInfo field.
Good question! I don’t know for sure.
Well, if you (or anyone else readying this) should every happen to figure it out, I would be very interested in knowing the syntax. I’m quite suck at the moment. Thanks.
RILEY – did you get this working – I am looking for assistance here as well – importing pbix file
I did not get this resolved. I ended up moving on from it because I couldn’t find any help getting the syntax to work. We’re starting to use Power BI much more, so the need for republishing Excel workbooks happens much less frequently. Sorry.
Well, I’m back to trying to figure this out. Adam, did you ever come up with a solution?
nothing yet – working w/ microsoft on this though – hoping for a fix soon and can reply here
In my case it only worked when I used the action RefreshDatasetInGroup, sendin the groupId along with the datasetId, otherwise it was giving 404 item not found, even after trying several times.
I tried to grab the latest swagger file and the link is broken – there is a swagger.json file there but Power Automate barfs when trying to upload that file saying its not in the proper format. Is there a different location for a correctly formatted JSON swagger definition file?
Same problem as Jordan, link is not good any more. Says page not found
sorry i made it where you have to register to download but i do have a copy of the working file here: https://vizbp.com/downloads/power-bi-automate-api-connector/
Looks like the Swagger definition is now here: https://github.com/microsoft/PowerBI-CSharp/blob/master/sdk/swaggers/swagger.json
Could you please advise on taking an backup of data from the one particular table from the model for last month from API Source .(ex. Help desk application)).
I am limited to minimum amount of records in API source (say 1 month), hence I will take backup of each month in SharePoint folder. Later stage i will merge the historical SharePoint data & current months data for complete report (for entire period selected)
Hi Chris, how do I authenticate against MS? I’ve imported the swagger file but whatever option I try I always get a “Forbidden(403)” error. Full response here:
{
“cache-control”: “proxy-revalidate”,
“content-length”: “0”,
“date”: “Tue, 15 Sep 2020 14:22:57 GMT”,
“requestid”: “f2bd87c6-8efc-4219-b7a4-c00e1df28c06”,
“strict-transport-security”: “max-age=31536000; includeSubDomains”,
“x-content-type-options”: “nosniff”,
“x-frame-options”: “deny”,
“x-ms-apihub-cached-response”: “true”
}
Going through Konstantinos’s original article (https://medium.com/@Konstantinos_Ioannou/refresh-powerbi-dataset-with-microsoft-flow-73836c727c33) and I’m currently stuck in the Security page – it seems the OAuth2 menu has changed a bit since this came out. Does any one have an updated version on what config options I should be going for?
Figured it out – must have messed up reading the instructions. Most of this was due to adding permissions to the app, mainly the Tenant.Read.All permissions.
Can you revisit this one please Chris?
There seems to have since been more changes; now we have 228 actions! But also a few traps that cause issues when creating the connector
were u able to import the file and create the connector? I too am seeing errors when creating connector so wont fully save.
Not working for me still. It would be great to work it out.