cancel
Showing results for 
Search instead for 
Did you mean: 

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

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:wgSmiley Surprisedauth:2.0Smiley Surprisedob

 

 

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

Comments

Awesome!

Is there a reason you couldn't use the HTTP directly in Flow to post via the Power BI REST API?

 

EX : Flic --> HTTP POST directly to the Power BI API?

I've been jiggering with a similar solution - A button Flow to trigger a GET statement, but I've run into a wall with authentification. I'm thinking I can't pass Power BI Access tokens, but my knowledge here is limited. 

 

Any thoughts?
Been trying to create : 

Button Flow --> HTTP GET (Get all groups, datasets, reports, etc.) --> Write all objects in JSON to SharePoint lists.

Hi,

 

You can create the http post or get command in the HTTP step in flow as well.

 

I just prefer to wrap it in a azure function to get better debugging and reuseability using the azure function.

 

So I would recommend you to use the azure function to build you idea.

 

You will also be able so store the credentials in the azure function to make it easy to use those in your solution.

 

/Erik

 

Meet Our Blog Authors
  • Working daily with Microsoft Cloud to deliver the needs of my company, my customers and various Microsoft communities and forums. | Office 365 | Flow | PowerShell | PowerApps | SharePoint |
  • Co-founder of https://plumsail.com, Office 365 and SharePoint expert. Passionate about design and development of easy to use, convenient and flexible products.
  • Microsoft Business Apps MVP. Owner of ThriveFast, an Office 365 consulting company.
  • 7x Microsoft Business Solutions MVP (CRM)
  • I'm keen in MS technologies, SharePoint, Office 365 and development for them
  • Daniel is a Business Productivity Consultant & Microsoft Business Solutions MVP who is very enthusiastic about all things Office 365, Microsoft Flow, PowerApps, Azure & SharePoint (Online). Since the preview, Daniel has been working with Microsoft Flow and later on with Microsoft PowerApps. That led to him being awarded an MVP Award for Business Solutions. He loves to blog, present and evangelize about improving productivity in the modern workspace with these amazing tools!
  • Michelle is an Office 365 solution architect in Twin Cities, MN. She has been delivering business collaboration solutions for years with her focus on SharePoint and Office 365. Michelle is a recent board member of the Minnesota Office 365 User Group and has been a member of the SharePoint community since 2009. She is a frequent speaker at MNSPUG and SharePoint Saturday and co-chaired the Legal SharePoint User Group for 4 years. Her most frequent projects have involved rolling out a large deployment of Office 365, SharePoint Online intranet, build of a "CHAMPS" Office 365 user adoption program and most recently, SharePoint On-Premise to Online Migration. Michelle is very excited about cloud technology as it is shifting her IT Pro focus to collaboration strategy and technical adoption.
  • I'm a Microsoft Office Servers and Services MVP with a special interest in SharePoint, Office 365, Microsoft Flow, Microsoft Teams and PowerApps. I work at Triad Group Plc ( https://triad.co.uk)
  • Passionate #Programmer #SharePoint #SPFx #Office365 #MSFlow | C-sharpCorner MVP | SharePoint StackOverflow, Github, PnP contributor