cancel
Showing results for 
Search instead for 
Did you mean: 
donsvensen

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

Comments
Anonymous

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

 

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/