Skip to content
August 16, 2017 / Erik Svensen

Updating a #PowerBI dataset using Microsoft #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

Let me know if you find this interesting.

Advertisements
May 8, 2017 / Erik Svensen

Speaking at SQL Saturday #588 NYC #SQLSATNYC

I still hasn’t really sunk completely in but I have been selected as a speaker at SQL Saturday #588 in New York.

The session line up is amazing and with a lot of Power BI celebs – both Adam Saxton and Patrick Leblanc are speaking as well – so I fell a little nervous but extremely proud

My session is called “Power Query – Don’t be afraid of the advanced editor” and will be about how you can make your Power Queries do even more and make them more dynamic and flexible. This can be done by knowing the M language a lit bit better and use it in ways you perhaps hadn’t thought of before.

I will show some of the cases and examples where the interface in the query editor wasn’t capable of solving my problems but using the advanced editor could.

So, join me on a ride into the advanced editor and hopefully I can teach you how to

  • Use variables in M
  • Refer to other queries and columns
  • Make a dynamic date table
  • Handle different number of columns in your datafiles
  • Handle different headers in your datafiles
  • Discover some of the hidden parameters in the M commands

 

Read about the event here – http://www.sqlsaturday.com/588/eventhome.aspx

 

 

April 23, 2017 / Erik Svensen

Set size for multiple visualizations in #PowerBI at the same time

When designing your reports in Power BI Desktop you properly spent a lot of time making sure your visualizations is aligned and at least for some of them making sure they have the same size.

So far, we only have the align feature in the Power BI Desktop

To change the size of the visualizations we must use the General properties under Format to resize the elements

But what if you want to resize more than one element at a time – If you select more than one you get the size of the first selection in the general tab

Now here is the trick – modify the width and Height with 1 each

And then back again

 

And your visualizations have the same size.

OBS – This only works when you select the same type of visualizations – if select different types you won’t be able to see General under Format.

Hope this can help you too –

 

 

April 14, 2017 / Erik Svensen

Tip – Create several AutoSum Measures in with one click #Excel Power Pivot – #powerbi

Just wanted to share a quick tip I just stumbled upon when designing a data model in Excel Power Pivot.

For creating a measure in Power Pivot, I place the cursor under the column and then I click the AutoSum button on the Home tab –

And then I get the formula created quick and I can modify the name.

But did you know that if you multiselect several cells in the Calculation Area and click the AutoSum – you get formulas for all the columns you have selected

 

Hope you find this useful as well …

 

 

 

October 5, 2016 / Erik Svensen

Power Query is awesome – dynamic table of dates – #powerbi

Many blogposts has been written about how you can create a generic date table in your Power Pivot / Power BI datamodels.

Here is a few examples

https://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/

http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

https://www.powerquery.training/portfolio/dynamic-calendar-table/

But my challenge with these has always been that I had to specify from date and to date either in a worksheet, text file or directly in the Query Editor.

Normally your fact table actually contains the max and min date that you actually want your date table to contain – so in the last model I created I decided to see if I could make the from and to date dynamic based on the max and min date in the fact table.

By using a bit of M magic functions it turned out to be very easy.

Let’s begin

In my example I load the FactInternetSales table from the AdventureWorks database

Now as we can see in the query editor we have a column containing the Order date and the min and max of that column should be used to generate the table of dates.

Convert the OrderDate to Date instead of DateTime

Then add a blank query via the New Source button

In the formula bar you enter the following formula

= List.Dates(List.Min(FactInternetSales[OrderDate]),Duration.Days(List.Max(FactInternetSales[OrderDate])-List.Min(FactInternetSales[OrderDate])),#duration(1,0,0,0))

And you get a list of dates – 🙂

Now we can add columns with the date information we want in our date tableby using the add Column Date – From Date & Time

The date table now depends on the Query FactInternetSales – and with the September update of the Power BI Desktop we can see that via the Query dependencies window

Now Power Query apparently knows that it has to update FactInternetSales before it updates the Date table, so when our facttable is updated the Dates get updates afterwards.

You could by modifying the expression to get the list of dates calculate the first day of the min year and last date of the max year etc. – all this depends on how complete you want the datetable.

Power Query is AWESOME J

June 10, 2016 / Erik Svensen

Inserting a picture/logo using the Power BI online designer #PowerBI

When you design reports in PowerBI using the online designer and not the Power BI Desktop designer you will notice that you cannot insert a picture via the interface.

With the development speed of PowerBI we will properly get it soon – but until that you might be find this workaround to do it via the datamodel interesting.

Let us switch to the Power BI Desktop designer and open a new report to create a datamodel we can publish to PowerBI.

Inspired by Chris Webb on how to create a table using M – (link) I have created a list of pictures using the #table statement

And this gives me a table with a number of Power BI celebrities J and a link to their twitter picture.

Now set the column ImageURL Category to Image URL

Now I can save the Desktop file and publish this to Power BI.

And now you can use the table or matrix and insert the ImageURL field on to your canvas.

Remember to use the Visual level filters to pick the image you want.

 

 

 

 

 

 

 

June 9, 2016 / Erik Svensen

Short Cut Keys– that every Excel user should know

Here is a list of short cut keys that I almost use every time I work in Excel.

The short cut keys works in the English version of Excel – localized version may use other keys.

CTRL + ‘ – copies the content of the cell above and enter edit state of the cell – very handy when creating almost the same formula as the cell above

F4 – when you are editing a cell reference in the formula bar the key cycles through the absolute and relative for column ref and row ref

CTRL+ _ (underscore) – removes any border around the selected cells

CTRL + ENTER – when a range or multiple cells is selected and you press CTRL + ENTER when you input values or formulas the input will be inserted in your entire selection

CTRL + F1 – Hide the Ribbon

CTRL + 1 – Open the format cells dialog

Hope these can help you too – if you have other favourites please let me know in the comments below.