Designing R chart in #Powerbi just got a lot easier for a R novice

I have for a long time had the wish to use the power of R to create some fancy visuals in PowerBI, but I simply haven’t had the time to read the manual πŸ™‚

Then today I saw a retweet about how to make ggplot2 easily –

And thought that might be able to help me get started – AND IT SURE DID

The tools are still under development so expect a few bumps

First – RStudio

You need to install RStudio on your computer to support the Addin. So if you haven’t done so already – go to

To install the addin you can follow the instructions on the github site for the addin –

Simply insert this in code window and run it

R-Studio will then install the addin and all its help files etc.

Now we can run the addin by running this line


and the design window will pop-up

But the magic happens when we do it from PowerBI Desktop

So I created a small data model based on AdventureWorksDW2014 database – for the ResellerSales

And I plot all the resellers by Sales_Amount and Sales_Profit and use the business type as the legend.

Then I insert the same fields in an R visual

And in order for the R- Visual to show a chart we have to open the R-Script Editor at the bottom of the screen

And now I have to paste or type my R-script code here ….. and this is typically where I previously started to search on google for what I needed to do.

I would quickly find the library ggplot2 was needed to create a scatterplot but then I needed to find out a lot about formatting and legends and axis etc. and normally I would give up within a few hours perhaps

The addin to the rescue

It is actually possible to activate the AddIn from the R Script editor πŸ˜€

If we add this line to the editor – and click RUN – magic will happen


It loads the designer in the browser !!!

Our dataset is automatically available in dialog for choosing the dataset

635 records and 4 fields !!!

Choosing the dataset will give us a validation of the fields (OBS -Don’t use spaces in the fields you add !!)

We can now drag the fields to the different areas of axis, color and size and start to format the chart further

It will automatically switch between different types of visualizations depending on what you choose – and you can via the menus at the bottom do a lot of formatting and when you are done – you can open the Export & code windows and copy the code to the clipboard.

Then close the window and switch back to Power BI Desktop and paste in the code

Remember comment out the line esquisse::esquisser() by adding a # in front

Click on the Run Script and minimize the R Script editor and the R chart is now in your desktop – awesome!!!

The chart types supported in ggplot2 are these

And designing and using them just got a lot easier.

So I recommend that you follow and support them on github and help them fix bugs by reporting any issues.

Power ON!

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

Use Power Query to time Power Query

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


Source = DateTime.LocalNow()



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

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

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

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

Create a calculation of Time now.

Step 2

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”

Step 4

Rename the columns

Step 5

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

The full query is now


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}})


#”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 !!!!)

To time the refresh I click the Refresh button

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

And click refresh again.

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


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

Happy querying

#PowerQuery – Replace Values in one column with the value in another column

Just wanted to share a method in Power Query that might also help you. Today I had a table where the column contained information about both the parent element and child element

In the example the CategoryInfo column contains both the Category and sub Category information.

And I wanted it to be transformed into this

Let’s first add a conditional column that checks whether the row contains a Category and if so insert the text from the CategoryInfo Column

Next up is the Replace Values magic.

The Replace Values dialog doesn’t support referencing a column

In the example I just add a step where I search for C and replace it with “”.

This step is not good enough so I modify the function parameter to this

= Table.ReplaceValue(#”Changed Type”, each [SubCategory], null, Replacer.ReplaceValue, {“CategoryInfo”})

The second argument uses

each [SubCategory]

to use the value in SubCategory in each row at the “Value to Find”

The third argument uses


as the Replace With

The fourth argument is changed to


Instead of Replacer.ReplaceText so it will replace the whole value of the column to search in and replace it with null

This will give us

And now we can use fill Down to insert the CategoryInfo in all the null cells

And finally filter all rows where SubCategory is null to end up the final result

You can download the example file here – link

Power Query On !!!

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.

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

You can do it like this

Choose the rename report in the navigation pane.

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.


Hope this can help you as well.


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.

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

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


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

= 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

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

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.

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

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 =
SELECTEDVALUE(‘Dynamic Fact'[FactKey]),
1,[Sales Amount],
2,[Sales Profit],
3,[Sales Units],
4,[Product Cost],
5,[Profit pct],

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

Hide the table FactResellerSales

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

Use the dynamic fact

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

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

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

Create the Row Level Security

Now we need to add Row Level Security

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

So when viewing as NonSecretMeasures the user sees this

But when viewing as AllMeasures we see

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

Let me know what you think

Link to demo file – here