Tag Archives: #powerbi

Time your Power Queries – #powerbi #powerquery

Sometimes your power queries can get quite heavy and you might need to optimize the steps in your query but how can you calculate the time it takes for your query to load.

Wouldn’t it be nice if you could have Power Query to do it for you

 Time your Power Queries – #powerbi #powerquery

Use Power Query to time Power Query

 Time your Power Queries – #powerbi #powerquery

Well – the Query dependencies window gave me the idea – what if I had a query with the start time of the refresh and then made the sales table dependent on that and then a duration table that where dependent on the sales table

Steps needed

First a query that calculates when the refresh is started

let

Source = DateTime.LocalNow()

in

Source

This will use the DateTime.LocalNow() to set the start time

 Time your Power Queries – #powerbi #powerquery

Now in this example I am loading a Excel file on my local harddrive on 22,3 mb with appx. 365.000 rows.

 Time your Power Queries – #powerbi #powerquery

After a navigation step and a promoted header step – I add a Custom Column where I refer to the Query “Start”

 Time your Power Queries – #powerbi #powerquery

This will add the start date to all rows in a separate column and will make the sales table dependent on the Query “Start”.

Next, we need to calculate the duration in a query that is dependent on the “Sales” table.

Step 1

 Time your Power Queries – #powerbi #powerquery

Create a calculation of Time now.

Step 2

 Time your Power Queries – #powerbi #powerquery

Convert it into a table

Step 3

To make it dependent on Sales I add a calculated column that retrieves the MIN value of the values “Start” in the table “Sales”

 Time your Power Queries – #powerbi #powerquery

Step 4

Rename the columns

Step 5

 Time your Power Queries – #powerbi #powerquery

Now we can calculate the duration in seconds by using the function Duration.Seconds() and subtracting [End] and [Start]

Step 6

And finally convert it to a decimal value

 Time your Power Queries – #powerbi #powerquery

The full query is now

let

Source = DateTime.LocalNow(),

#”Converted to Table” = #table(1, {{Source}}),

#”Added Custom1″ = Table.AddColumn(#”Converted to Table”, “Custom.1”, each List.Min(Sales[Start])),

#”Renamed Columns” = Table.RenameColumns(#”Added Custom1″,{{“Column1”, “End”}, {“Custom.1”, “Start”}}),

#”Added Custom2″ = Table.AddColumn(#”Renamed Columns”, “Query Duration”, each Duration.Seconds([End]-[Start])),

#”Changed Type” = Table.TransformColumnTypes(#”Added Custom2″,{{“Query Duration”, type number}})

in

#”Changed Type”

Then I disable the load of Sales table in order not to have Power Query read the file several times – (OBS be careful if you already have created measures on the table as the disable of load will remove these measures !!!!)

 Time your Power Queries – #powerbi #powerquery

To time the refresh I click the Refresh button

 Time your Power Queries – #powerbi #powerquery

And the card I have inserted in my report will show the number of seconds the query took.

Now let’s see what a conditional column cost

So, in the sales Query I add a conditional column that does evaluation on every row using the contains operator

 Time your Power Queries – #powerbi #powerquery

And click refresh again.

 Time your Power Queries – #powerbi #powerquery

Depending on your scenario you properly run the refresh several times in order to see the effect on your query.

Comments

Please let me know if you have comments or have solved how to time your power queries in another way.

Happy querying

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

Change the order of reports in the #powerbi service

Just wanted to share a hack of the problem that you can’t determine the sort order of your reports in the service so they will also be sorted alphabetically.

But by using little trick you can overcome this – and not by using a number or label them A., B. etc. in front of the name.

 Change the order of reports in the #powerbi service

As you can see my “Demo” report is actually before “Buildings Con….” Report.

You can do it like this

 Change the order of reports in the #powerbi service

Choose the rename report in the navigation pane.

 Change the order of reports in the #powerbi service

And then simply a space before the name of the report – so if you have 4 reports the report you want to be listed first should have 4 spaces and no 3 should have added 3 spaces etc.

The interface will not show the spaces so it will still look nice – you might have to switch to another workspace and back again in order for the cache of the webpage to be refreshed.

 Change the order of reports in the #powerbi service

 

Hope this can help you as well.

.

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

7 Important Considerations When Implementing PowerBI with Microsoft Dynamics 365

CRM Blog 7 Important Considerations When Implementing PowerBI with Microsoft Dynamics 365

Traditional dashboards within Microsoft Dynamics 365 are powerful, but they do have some limitations. For example, a security model applies. (This can be a pro and a con depending on the analysis). There is limited visual reporting options and a 5000 record limit.

PowerBI and Customer Insights can provide some great additional functionality.  However, there are a few things to be aware of before starting a PowerBI implementation with Microsoft Dynamics 365.

Seven things to consider when implementing PowerBI with Microsoft Dynamics 365: 

  1. Version Selection: Be careful and choose the PowerBI version wisely (Free, Pro, Premium) due to data limits and restrictions!
  2. Refresh Limits: In order to replicate Dynamics 365 data to PowerBI you need to turn on the Data Export Service in your Dynamics Subscription. And you must have “Change Tracking” enabled.  Changes are pushed “near real time”, however based on the version of PowerBI there are limits to how often you can refresh.
  3. Storage Costs: If you want to use PowerBI and Customer Insights, it requires two separate data replication paths. Customer Insights runs on a Graph database and does not use the same data as PowerBI.  Likewise, the Common Data Model has its own data source, and is a third replication destination.  Each has its specific purpose, so it just means that you need to have a replication strategy and to limit data going to each source in order to control storage costs.
  4. Replication Strategy: Replicating Dynamics 365 data to PowerBI does add complexity to reporting due to the 365 Meta Schema versus a custom designed schema.  Some may choose custom replication options to store data in a more streamlined manner.
  5. Embed Options: While you can create links to Dynamics 365 records for drill down in PowerBI reports, PowerBI does limit iFraming of components inside of Dynamics 365.  So, for example, at this time, you can not embed a PowerBI report on an entity in Dynamics 365.  Just the dashboards. You can insert a “report button” or URL link which can launch PowerBI with a unique URL. Using the URL to pass Dynamics 365 data to the PowerBI client allows for contextual reports (assuming they are written that way.)
  6. Security Models: Sharing reports in PowerBI does not respect the Dynamics 365 security models. So if you share PowerBI reports, be careful!
  7. Sharing Costs: Sharing reports outside your organization adds cost.  Third party products provide this capability, but most charge every time the report is accessed, which can add up.

The team at Crowe Horwarth we can help you determine the right option for your business. And make you aware of all the important considerations, before the implementation.

If you are interested in evaluating Microsoft Dynamics 365 (formerly Microsoft Dynamics CRM) contact us today.

By Cullen Hunter, Crowe Horwath, a Microsoft Dynamics 365 Gold Partner www.CroweCRM.com

Follow us on Twitter: @CroweCRM 

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Move or Resize #PowerBI visuals with the arrowkeys

April 4, 2018 / Erik Svensen

Move or Resize #PowerBI visuals with the arrowkeys

You might already know that you can move one selected visual with the arrow key – one point and if you hold down the SHIFT key it will move 8 or 9 pts when you click the arrow key.

See this example – move the visual with the arrow keys


But can you also resize multiple visuals !!!

Until today I didn’t think it was possible to resize visuals using the arrow keys – but it can be done – and even when you select the more than one of the same type of visuals.

So, if you want to make all your cards or bar chart – you can simply select them and then switch to the Format tab of the visual – under General you will find the width and height of the selected visuals.

You can enter new values OR use the magic of the arrow keys !!!!!!! – if you use Arrow up or down you can actually change the number 1 point at a time

Check out this video

This will naturally also make your visuals exactly the same size.

It will save me and hopefully also you a lot of mouse clicks in alignment and resizing.

Let’s block ads! (Why?)

Erik Svensen

Extraction of number (or text) from a column with both text and number – #PowerQuery #PowerBI

March 6, 2018 / Erik Svensen

Extraction of number (or text) from a column with both text and number – #PowerQuery #PowerBI

When you are working with data in Excel or PowerBI the data often contains columns that is a combination of text and numbers.

One example could be like this

 Extraction of number (or text) from a column with both text and number – #PowerQuery #PowerBI

 

If you have this challenge you shouldn’t use Split Columns or Text.Range to do this but check out

Text.Select

Documentation here

And Chris Webb has good example using it for text – here.

My example demonstrates how to work with text but also works with numbers and capitals letters and symbols etc.

Here is how we can extract the House number and Zip Code – use the Custom Column from the Add Tab in the Query Editor window

 Extraction of number (or text) from a column with both text and number – #PowerQuery #PowerBI

= Table.AddColumn(Source, “Housenumber”, each Text.Select([Street], {“0”..”9″}))

= Table.AddColumn(#”Added Custom”, “Zip Code”, each Text.Select([Zip], {“0”..”9″}))

And now we have

 Extraction of number (or text) from a column with both text and number – #PowerQuery #PowerBI

And one other benefit is that the Function doesn’t return an error when there is no number in the string.

Here is an example file

Hope you find this useful

 

 

 

Advertisements

Let’s block ads! (Why?)

Erik Svensen

Hide measures using Row Level Security – #PowerBI

March 3, 2018 / Erik Svensen

Hide measures using Row Level Security – #PowerBI

In some cases, you might not want to give all users access to all measures in your model – you might not want to show the profit to certain users.

In the Power BI Desktop designer/Service we can’t hide measures depending on the active user but by combining dynamic measures and row level security we can make our way around this.

In my example I use data from AdventureWorksDW2014 and created a datamodel around FactResellersales.

 Hide measures using Row Level Security – #PowerBI

So, we have 5 measures but Sales Profit, Product Cost and Profit pct should be hidden for some users.

First up creating a dynamically fact

I created a table by entering data in a table

 Hide measures using Row Level Security – #PowerBI

The column Secret should be used to use to filter by user and FactKey we will use the FactKey in a SWITCH statement to create a dynamic fact.

The dynamic fact

The fact will be created like this

Selected Fact =
SWITCH(
SELECTEDVALUE(‘Dynamic Fact'[FactKey]),
1,[Sales Amount],
2,[Sales Profit],
3,[Sales Units],
4,[Product Cost],
5,[Profit pct],
BLANK()
)

In the model I hide all the columns and only show the fact

 Hide measures using Row Level Security – #PowerBI

Hide the table FactResellerSales

To disable the user to be able to select any of the measures created in the FactResellerSales.

 Hide measures using Row Level Security – #PowerBI

Use the dynamic fact

So, in order to use the fact we have to tell the visual which fact to use.

 Hide measures using Row Level Security – #PowerBI

In this case a card visual I have selected the fact.

You can also use the matrix to show more facts at once

 Hide measures using Row Level Security – #PowerBI

Create the Row Level Security

Now we need to add Row Level Security

 Hide measures using Row Level Security – #PowerBI

NonSecretMeasures is now set to filter out the Facts where [Secret] is set to True.

Test the RLS

We can now test the Row Level Security in Power BI Desktop designer

 Hide measures using Row Level Security – #PowerBI

So when viewing as NonSecretMeasures the user sees this

 Hide measures using Row Level Security – #PowerBI

But when viewing as AllMeasures we see

 Hide measures using Row Level Security – #PowerBI

Scaling it – consider moving it to Azure Analysis Services/Tabular model

This method doesn’t really scale very well but can be used in small models.

If your model is bigger and more complicated, you should look at building the model using Azure Analysis Services or a On Prem tabular model where you can implement object level security.

Q & A can help

Using Q & A in the report – it makes it a bit easier to create the visuals

 Hide measures using Row Level Security – #PowerBI

Let me know what you think

Link to demo file – here

Advertisements

Let’s block ads! (Why?)

Erik Svensen

Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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.

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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,

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Wait for the deployment to finish – it takes about 1-2 minutes.

Choose to create your own custom function

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Select language Powershell

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

And the HttpTrigger

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

By clicking Get Function URL

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Now – Microsoft Flow

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and 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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic 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.

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

As you can see the flow ran as 23:25

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

And when checking the refresh history in Power BI we can see its refreshed –

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

And I get an e-mail as well

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

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

Filtering Errors in PowerBI

I just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive an expression error. Let’s build an example filtering errors in PowerBI.

The image below illustrate a small set of data in excel that will result in this problem. The objective is to extract the numbers and exclude all the rows without numbers. 

PBIError1 Filtering Errors in PowerBI

After importing the excel file in PowerBI, let’s extract all the content after the “=” sign using Transform => Extract=> Text After Delimiter. 

PBIError2 Filtering Errors in PowerBI

PBIError25 Filtering Errors in PowerBI

PBIError35 Filtering Errors in PowerBI

Now, let’s change the data type to decimal using Transform => Data Type

PBIError3 Filtering Errors in PowerBI

We have two errors in this resultset, as you may notice in the images below. There is no way to exclude these errors using the interface, we need to code in M to achieve this. There are two useful functions available to filter errors: Table.RemoveRowsWithErrors and Table.SelectRowsWithErrors, the names already explain what they do.

PBIError4 Filtering Errors in PowerBI

PBIError5 Filtering Errors in PowerBI

We need to open the Advanced Editor, inside View, to edit the M code.

PBIError6 Filtering Errors in PowerBI

PBIError65 Filtering Errors in PowerBI

We will add a new line before the “In” statement. In this line, we will create a new name for our result, like this:

After the “=” sign we can use the function, it has two parameters: The name of the result of the previous line and the column we want to check for errors.

Two more details to go:

  • The previous line need an extra comma at the end
  • After the “in” statement, we need to change the name of the final result set to our new name

PBIError7 Filtering Errors in PowerBI

The final result, without the errors:

PBIError8 Filtering Errors in PowerBI

Let’s block ads! (Why?)

SQL – Simple Talk

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

April 23, 2017 / Erik Svensen

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

 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

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

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

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

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

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

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

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

And then back again

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

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 –

Advertisements

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

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 –

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

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

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

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

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

Hope you find this useful as well …

Advertisements