Update a PowerBI Dataset using Flow, Azure Functions and a Flic Button

[donsvensen]

I am working on a project where customer would like to update a PowerBI dataset when specific events occur – in their case when an email with a specific subject arrives in a specific mailbox – and we succeeded by using Microsoft Flow, Azure Functions and PowerBI REST API. But as I recently bought flic buttons (https://flic.io/) so I wanted to change the setup a bit so the dataset is updated when I click the button – sort of my first IOT project.

The ingredients

• 1 dataset in your PowerBI tenant that can be scheduled for refresh
• 1 Power BI Pro license (or premium if you want more than 8 refreshes a day)
• 1 Registration of an Application for Power BI ( link - https://dev.powerbi.com/apps )
• 1 Azure subscription
• 1 Power Shell script
• 1 Azure Function/Logic App
• 1 Microsoft Flow license in any tenant
• 1 Flic button
• 10-15 minutes

First up – the information about the dataset and the group

We need the dataset ID and group ID where the dataset is placed. The easiest way to find it is to navigate to the settings and the dataset tab and click the data set – the group ID is in blue area and the dataset ID is in the red box,

Notice that the Scheduled refresh is set to Off.

Get a client ID for your Power BI Application

You will need to register an application for the update and you can do this via Sign in and fill out the information.

The App Type has to be Native app and the Redirect URL must be - urn:ietf:wg:oauth:2.0:oob

Select both the Dataset API's and in step 4 click to register the App and save the Client ID.

Powershell script to update the dataset

You can find the Powershell script here - https://github.com/Azure-Samples/powerbi-powershell/blob/master/manageRefresh.ps1

Fill in the $ClientID, $groupID and $datasetID in the script.

In order to avoid popup windows with authentication in the Azure Function I had to modify the script and hardcode the username and password in my script.

$userName = "username"

$password = "password"

$creds = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential" -ArgumentList $userName, $password

$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $creds)

The bold lines are added and the $authResult is modified as well.

Azure Function

Log on to the Azure portal - https://portal.azure.com/ and create a Function App

Wait for the deployment to finish – it takes about 1-2 minutes. Choose to create your own custom function

Select language Powershell

And the HttpTrigger In my example,

I will keep authorization level to anonymous – but you can add more security if needed.

Now replace the script with the PowerShell script from the previous section.

Now you can save and click run to see if the script works

If the script succeeds – you can check whether the dataset is updated via the Power BI dataset refresh history

By clicking Get Function URL

We will get the POST Url that we will use later in our Flow.

Now – Microsoft Flow

Open your Microsoft Flow in your Office 365 portal and create a new blank flow

Now we can select between a lot of different triggers that can activate our data refresh in Power BI.

It could be certain tweet, a mail in an inbox or in onedrive and many more – we currently have 160 triggers to choose from.

In my example, I want to integrate with a flic button

And by connecting to my Flic account I can choose the button I want to use and which Event (it has click, double click and hold) it should be attached to on the button

To activate the refresh, I add a new step as an action I choose the HTTP where I can post the URL to the function.

After the HTTP action, I add a new step that sends and email to me – with information about the refresh and which button was presses and where it was located when pressed. Then save the flow and we should be ready to update the dataset via the button

Trying the flow

So by clicking the button we can see the Run History

As you can see the flow ran as 23:25 And when checking the refresh history in Power BI we can see its refreshed - And I get an e-mail as well

OBS - The refresh can "only" be done 8 times with a Power BI Pro License but 48 if you should be so lucky that you can have access to a premium edition.

Wrapping up

I have been so fun to create this and it was surprisingly easy – with off course some challenges underway. But the toolset is amazing and combining Flow and Power BI opens a lot of possibilities and ideas to activate refreshes and do stuff with the Power BI REST API triggered by different events. I will at least have an extra look at the API and see what can be done – link to documentation