#PowerQuery everywhere :-) – now in #microsoftflow as well

In the Microsoft Flow October 2018 update – link – we got a very interesting feature added to Microsoft Flow – Power Query and as they state –

Also, to better empower our users – we have integrated Power Query directly into the product. Power Query is a data ingestion tool that allows power users to transform, filter, manipulate, and import data into popular data stores like Power BI and CDS. We are adding this same capability to Microsoft Flow that will allow makers to shape data mashups from SQL Server using a Power Query Online experience.

So, I decided to try it out

You can find the Action by searching after “Power Query”

So far, we only have one possible data source– which is the SQL server (but there is more) – so selecting the action gives us this

You can either pick an existing SQL server connection or create a new connection via the dots…

Clicking the Create Query open the Power Query Editor and we can start our transformation/selection of data

And when we click next we get too the Query Editor

Notice that the Server and Database is created as Parameters.

OBS!!!

I haven’t tested it but when you right click the pane you actually get the full “Get data” experience – and when you choose one of these you have to use a Enterprise Gateway to use this.

We can now filter the rows

And even go to the advanced editor when you right click the Query name.

Important

You can only return ONE query to your Flow – so be sure to only enable one of your queries.

Getting it into Flow

When we click the Update button we just get back to our flow

And now we need to add an action that uses the result of the Power Query and we can pick between one of the many possible actions

But just to try let’s try and put the data into Power BI

So, I create a streaming dataset in My Workspace –

And remember to turn the Historical data analysis on

Now as the Query can return more than one row we first Add and “Apply to each” step

And then find the Action “Power BI – Add rows to a dataset”

Now we can pick the workspace – dataset we want the rows add to and bind each column to the result from Power Query

Now we are ready to test our flow

And after the connections is validated we can

Hit continue

And the flow starts

We can follow the progress from the Run history

And entering the step – we can check the output

So, as you can see its JSON document.

And we can start to build a report in Power BI

Opportunities

I think this opens for a lot of different opportunities for utilize the incredible Power Query language to extract and transform data from almost any where and then perform actions – like exporting data, sending notifications via e-mail, create task or alerts based on the data and ….

I am pretty sure that we will have great use of this and can’t wait to see what users will use this for.

Happy Power Querying !!!

#powerbi – Switching a report from one PowerBI dataset to another PowerBI dataset

At the Power BI world tour in Copenhagen last week I was asked how to change a Power BI report’s connection from a dataset in the service – for instance if you have a test workspace and then want to move the report to a production workspace.

I started to refer to my previous blogpost – https://eriksvensen.wordpress.com/2017/10/03/how-to-change-the-powerbi-desktop-file-connection-from-data-model-to-a-power-bi-service-dataset-or-azureas/ – but when using this hack it won’t work.

So we have to do it in another way and it is quite simple – one important thing is of course that the dataset has the same dimensions and measures – otherwise some of your visualisations will show an error.

When we create the report we can select a Dataset from the service

When we save the report it will automatically be saved in the workspace where the dataset resides.

But how can we change it –

If you go to Edit Queries

You can click Data source settings – then we will get the option to pick a dataset from another workspace

And then you just pick the dataset you want to use.

When you save the report now – the report will be saved in the workspace where the selected dataset resides.

Hope this can help you as well – and if so give this post a like

#PowerQuery – Change the Column name to the index of the column

Inspired by Ruth Pozuelo from Curbal latest video – https://www.youtube.com/watch?v=jlogBrcYZwc&t=2s where she shows an excellent tip on how to change column headers to upper or proper case –

And the trick is to use the second argument in the Table.TransformColumnNames – and for instance use Text.Upper to sett all names to capitals.


You can also use

Text.Lower

Text.Proper

I decided to check if I could use some of the other text functions to do even more 🙂

So why not see if we could give each column the name of the index number they have in the query.

And it turns out that it’s possible

If you add the following step

= Table.TransformColumnNames(Custom3, each “”)

The each statement will to the renaming for each column and name it to an empty string – and if you have more than one column with the same name it will get a index number added as a suffix – so column 2 will be name empty string + 1 and so on.

In order to rename the columns to start with 1 instead of an empty string we can use the TransformColumnNames once more but this time to add 1 to each column names – but as column names is text we need to convert it to a value and back to text again

= Table.TransformColumnNames(#”Custom1″, each if _ = “” then “1” else Text.From(Value.FromText(_)+1))

And now we can refer to cells in our table using coordinates

Hope you find this useful and happy querying 🙂

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