Microsoft Flow Automation using Power BI API

After presenting this session for the first time at #PBIG Utrecht 30th of March, I got some feedback that this was very useful and something that more people wanted to test (Shoutout to the peeps attending my session and my Twitter Network). So why not write a blog post about it! I would like to thank all the Organizers from the PBIUG Netherlands, you did a great job. I will definetly recommend to speak or attend, it's one of a kind. Also a big thanks to Avanade Global for sponsoring my trip.

PBIG2019 Utrecht

The Power BI REST API provides service endpoints for embedding, administration, and user resources. You can find all of the APIs here. In this Scenario I will leverage the datasets and capacity API's, but you can use all APIs, depending on your needs.

Microsoft Flow is a low-code, no-code automation tool. It provides the citizen developer with a tool to automate directly from business and productivity apps. You can access Flow from O365 or www.flow.microsoft.com.

Now what happens if we bring these two together? Simply magic.

The first thing you have to do to register your application in Azure Active Directory

... and give access and rights to the APIs you need. Remember to copy the application ID and secret for later.

Remember that if you do not have the rights to grant permissions in Azure, that might have to be done by a tenant admin for that registred app. This is a very common root cause for authentication errors based on my experience. You can also register an app and add API rights directly in Azure.

Create Custom Connector and Actions for Power BI API

If you do not wish to go the steps manually, there is also a swagger definition here. You can then instead of using the “Create from blank” option, use the “Import an OpenAPI file” option instead and upload the Power BI REST API OpenAPI definition file. Then you do not have to create all the actions from the first demo (Shoutout to Chris Webb, he has a blog post about it here).

I will anyways show you how to do this manually, as I think it's a great learning. First we head over to Microsoft Flow. These are the steps from the video:

1) Create new connector From blank.

2) Remember to add Host: API.PowerBI.com

3) Add Authenication Type OAuth2 and Identity provider: Active Directory

4) Add App ID / App Secret (Client ID/Secret) that you copied earlier

5) Add Resource Url for Power BI API

6) Save

7) Copy redirect url

8) Go to azure – Find your app registration – Add reply urls

9) Add redirect url copied from step 7

Add actions to the connector that you want to use in your Flows

In this case I will add the refresh Dataset in «My Workspace», App Workspace Dataset Refresh, Add Get refresh history from workspace, Get Capacities and AssignToCapcity (App workspace). I will only provide the video from the refresh dataset in my workspace (then that can be repeated for the rest) and assign to capacity (which has a slight difference from the other actions).

In the example to the left you see the added action for refreshing a dataset in "My Workspace".

We also would like to test the connector after adding an action, to see if it works. That can be done from the custom connector Test pane as shown below:

After the connector has tested successfully, and all the actions we need is added, we can start creating our Flows to automate processes.

Create a Flow Trigger--> Refresh dataset --> Get refresh history --> Post to Teams

The scenario is based on a need for triggering a refresh action based on something. In this case a tweet is the trigger for the refresh, but the trigger can be when a row is updated, added, etc. etc. Whatever suits your end-users/clients needs. Further we would like to get the refresh history for that workspace, and for example post it to a Teams Channel using the Flow Bot.

The Trigger is used to invoke, call or launch the Flow when a specific App event occurs.

The Action(s) are the step(s) executed once the Flow App Trigger event occurs.

1) Start Flow from blank

2) Add a trigger for when a new tweet is posted with search text #refreshmypbidatasetasap

3) Add custom action to refresh pbi dataset

4) Add parameter to group and dataset that you get from pbi service (url)

Assuming we have already added the Get Refresh History from Workspace action in a previous step, we go ahead and further expand the Flow to Get and Post Refresh History to Teams. I would recommend to query the output to for instance getting the latest refresh, and perhaps formatting it in a better way then what I did in this example.

Then test your Flow! And cross your fingers..

Capacity management and automation using Flow

A common scenario I see, is to have the ability to somehow govern which Workspaces are assigned to Premium Capacities. It might be that an organization has not rolled out Premium org wide (yet), or are in the process of testing out Premium and would like to have some level of control of workspaces that are assigned. In this Flow, a Form response is the Trigger used to invoke an approval process, if the request is Approved (by someone governing Power BI f. ex) the record will be stored in a SP list, and the Workspace will automatically be assigned to a Premium Capacity. Once the Workspace has been assigned, we would like to update the SP list item Status to "Assigned".

Based on Form submission --> Start approval--> If approved--> Create SP list item and Assign to capacity --> Update list item if successful

Here are the Actions you need to take*:

• Create a form to collect information about workspace ++

• Create a SP list with fields you would like to store

• Create a flow using this template*

• Customize it as you’d like, and add Get capacities from workspace and add assign to capacity action from the custom connector

*Make sure you have the right permissions in Azure AD (read/write to capacities, admin right to capacity and workspace). In this demo I have used an A1 Sku in Azure as they are the cheapest option, and it can be paused and started as it suites me. To unassign the specified workspace from a capacity, Empty Guid (00000000-0000-0000-0000-000000000000) should be provided as capacityId.

Add Action AssignToCapcity (App Workspace)

(Assuming you already added "Get Capacities" in your connector). What I would like to emphasise in this Action added, is that in this case the Body also has a parameter for CapacityID. That CapacityID you insert, is the one that you get from the "Get Capacities" (if there are multiple you would have to input one).

Next we would like to create our Flow

We now have everything we need to go ahead and create the Flow.

In the video below I don't go into the details, but I have conected to my FormID in the Trigger. I checked that the workspace is not in a Premium Capacity already. Triggered by the form responses the Flow will kick off an Approval process. I have customized what to be shown in the Approval sent. Might be a good idea to include some data, so it's easy for the Approver(s) to understand what he/she should approve. If the response is Approved, I would like to store it in a SP list prior to the assignment to a Premium Capacity. I have mapped my SP list columns to the dynamic datafields that I would like to store in the list. I then get the capacityID from the form response and assign that App Workspace to the Capacity I have chosen. If the workspace is successfully assigned, I want to update the "status" field of the stored SP list item to "Assigned".

This might seem more complex than it is, honestly. So I really encourage to go ahead and try this yourself.

Then in the end, let's test our Flow!

For all PBI Flows

1. Register an App (Azure AD) Remember to grant permissions to PBI API (Admin)

2. Create a Custom Connector to PBI API

3. Add Actions to the Custom Connector for PBI API endpoints

4. Test the Custom Connector Action

5. Create Flow(s) with triggers, approvals, conditions and actions

6. Test the Flow

7. Publish!

Good luck with your Flows! And if you have any questions, please reach out.

Best regards,

Ida | Twitter: @IdaBergum | LI: https://www.linkedin.com/in/idabergum/