Calling The Power BI REST API From Microsoft Flow, Part 1: Creating A Flow Custom Connector

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.

image

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!

24 responses

  1. 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?

      • 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?

  2. Hi Chris – unable to find the OpenAPI definition file for the Power BI REST API at the link behind “here”… was that a mistake?

  3. Pingback: Calling The Power BI REST API From Microsoft Flow, Part 2: Refreshing A Dataset When a Data Source Changes « Chris Webb's BI Blog

  4. Pingback: Calling Power BI REST API From Microsoft Flow – Curated SQL

  5. Pingback: Calling The Power BI REST API From Microsoft Flow, Part 3: Finding All Data Sources Used In A Power BI Tenant « Chris Webb's BI Blog

  6. 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.

  7. 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.

      • 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.

      • 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.

  8. Pingback: Publicar en Power BI desde Microsoft Flow - dataXbi

  9. 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.

  10. 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?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: