Posted on by sqlarcher

Logic Apps meet Power BI

Recently I was looking for a solution to refresh a Power BI report while it is embedded in a web application, but doing so in a way which minimizes the requirement for code changes for the web application (this is only really useful when you use native Power BI embedding).

After searching for a while, I found the following post from Konstantinos Ioannou (Blog | Twitter), Refresh Power BI With Microsoft Flow. This looked like something that could work, but I do not want to use Microsoft Flow. Having worked with Azure for more than 4 years now, I know that Microsoft Flow is actually based on Azure Logic Apps, so there is definitely a way to port some of this across to Logic Apps.

I am not going to detail all the steps, since I feel they are sufficiently covered by Konstantinos’ blog post on the subject, and will only detail the steps required for Logic Apps.

Create an Azure Logic App Custom Connector

In order for us to be able to use the Power BI API’s, we start of by creating a custom connector and giving at a Name, Subscription, Resource Group, and Location:

Next up, we just edit our custom connection, we will leave the first section on how we want to create the connection blank.

General

For the general settings, you can give it an icon, short description, etc. These are optional, as we are only interested in the Scheme, Host and Base URL fields.

  • Set the Scheme to HTTPS
  • Host to api.powerbi.com
  • Base URL as a single /

Security

The following list is the required configuration.

Important! Save the redirect URL as you will use it later.

Definition

For the definition of the API we can give it some general information, and an action name. In my case, it will be RefreshGroupDataset.

Next, we need to define the request. The simplest method is to just head over to the Power BI REST API documentation, and copy the URL, and use the “Import from Sample”

Paste the URL in the URL field, ensure that you remove the “POST” if you copy it from the documentation as we set the verb in the radial selection.

Once you are done, then you will be left with the following:

The curly braces indicate that we are passing a variable, thus making the connection reusable when we want to refresh datasets in different groups.

Now you just need to update the connector (top right of the page) and it concludes setting up the custom connection for use in Logic Apps. One step which is not shown, is that the reply URL needs to be added as a reply URL in the application list. This step is the same as when configuring Microsoft Flow to refresh a Power BI dataset.

Building the Logic App

Now we deploy a new Logic App, I am just going to call it PowerBIRefreshAPI.

In the Logic App Designer, just start with a template for when a HTTP request is received (this will be set to a GET request).

Since we have created a custom connection, we now have the option to use it by clicking on +New Step, and select custom.

After selecting your custom connector, it will show you all the actions, in this example if you have been following along there will only be one called RefreshGroupDataset (or the name you provided as part of the definition step in creating a Logic App Custom Connection.

Selecting your action now prompts you for sign in. This need to be an account with a Power BI Pro license. You will also get a pop-up from the application created earlier (not shown in this post) asking for permission to read your profile.

Now, to test this out, I have a simple Power BI report, importing data from a table. After uploading this to the Power BI Portal, get the groupid and the datasetid in order to test the Logic App. An easy way to get this, is to just navigate in the portal to the dataset you want to refresh, and copy the GUID’s from the URL:

Use the values you copied, and save it into the Logic App. Take note, that once you save the Logic App, it will generate a unique https link for the http request trigger action. Copy this link to use in your Power BI Report.

Power BI

Now you might want to use Logic Apps as another way to manage refreshes for Power BI, but for my initial exercise as stated, was to find some way to refresh the report when it is embedded, with minimal effort. So how do we go about achieving this?

By using buttons in Power BI of course. We now use the the http trigger from the logic app, as a Web URL action inside of Power BI.

Clicking this will result in the report being refreshed, but there is an unfortunate event of opening a new browser window with the URL to the Logic App. As you can see in the screenshot below, my app did refresh though.

Conclusion

It is definitely interesting to see that this could be done, someone out there might even write a custom visual for Power BI for doing a refresh. If it wasn’t for the new browser tab being opened, then this could’ve been a nice solution to refresh a Power BI report from within the report pane itself.

For me, this was an interesting way of doing it, and maybe this also serves as a brief guide for others who have found some of the posts relating to Microsoft Flow, but wanted to use Logic Apps instead.

2 Replies to “Logic Apps meet Power BI”

Leave a comment