#powerquery – How to handle different decimal separator when importing csv files

Recently I have been working on a project where the solution should import a csv file exported from a SQL server. For some reason sometimes the data comes with a , (comma) as the decimal separator and other times with . (dot) as the decimal separator.

This meant that when importing the different files, I had to find a way to dynamically change the culture code setting when importing the file.

Let’s try and open the SalesWithDot.csv file in Power BI Desktop

As my Power BI Desktop uses Danish settings and danes uses , as a decimal separator Power BI desktop will think the values is 30.000 instead of 300 etc. – and we have to tell Power Query that the source is from another Culture – so I click Edit

As we can see from the documentation Table.TransformColumnTypes – the function has an optional parameter called Culture –

And by adding “en-US” to our formula bar we can have the right value in the SalesValue column

But when we use this when importing a file where the sales value is formatted with a , as the decimal separator and we use the same culture value (“en-US”) then we have the problem.

And by changing the culture to da-DK it shows the right values

So how can we make Power Query dynamically determine which local to use ?

In this case I know that the column SalesValue will either contain a comma or a dot – and by checking the first value of the file imported I can select which culture to use – this can be done like this

The step before we “Changed Type” is called “Promoted Headers”

To check whether the Sales Value contains a comma – we can use the Text.Contains function

And we can refer to the first value in the SalesValue column like this

#”Promoted Headers”[SalesValue]{0}

Then

Text.Contains(#”Promoted Headers”[SalesValue]{0}, “,”)

Will give us true if the cell has a comma in the cell before its changed to a decimal value.

When we know this we can change the

= Table.TransformColumnTypes(#”Promoted Headers”,{{“Product”, type text}, {“SalesValue”, type number}, {“SalesUnits”, Int64.Type}}, “da-DK”)

To

= Table.TransformColumnTypes(#”Promoted Headers”,{{“Product”, type text}, {“SalesValue”, type number}, {“SalesUnits”, Int64.Type}}, if Text.Contains(#”Promoted Headers”[SalesValue]{0}, “,”) then “da-DK” else “en-US”)

The if statement will then use the da-DK culture if the SalesValue contains a comma.

And in the example file with the dot

You can download an example file here.

Hope you find this useful – Power On!

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 https://www.rstudio.com/

To install the addin you can follow the instructions on the github site for the addin – https://github.com/dreamRs/esquisse

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

esquisse::esquisser()

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

esquisse::esquisser()

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

let

Source = DateTime.LocalNow()

in

Source

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

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

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.

Comments

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

null

as the Replace With

The fourth argument is changed to

Replacer.ReplaceValue

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

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

= 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