Just a quick tip that you might not be aware of in the Power Query Editor.
If you select two columns in the view and on the Add Column tab, select the Date button – you can select to Subtract Days
This will give you the number of days between the dates in the selected columns
Use the formula bar to rename the new column by modifying the step
Hope this can help you too –
Just wanted to share a M function that I just created to calculate the date from a Year and a ISO week number.
The example data is the following
Now the function I created can be called with the Year and Week number as parameters to get the following result
The function has the following code and will return the date of the Monday of the week number.
(TheYear as number, TheWeek as number) as date =>
let
//test
//TheYear = 2018,
//TheWeek = 1,
//
offsetToISO = Date.AddDays(#date(TheYear,1,1),-4),
dayOfWeek = Date.DayOfWeek(offsetToISO),
offset = -dayOfWeek + (TheWeek * 7),
isoWeekDate = Date.AddDays(offsetToISO, offset)
in
isoWeekDate
Hope this can help you too.
Here is a link to an example pbix file – link
Image a scenario where your column in your PowerQuery that contains a table with a set a columns that you know at some point will have more columns added.
In the case above I know that we at some point will add more columns in the merged ProductAttributes table.
When we click the icon for expanding the table, we might just select this and move on
But notice the formula created in
It says
= Table.ExpandTableColumn(#”Merged Queries”, “ProductAttributes”, {“Brand”}, {“Brand”})
This means that even though we might add new columns to the ProductsAttributes table – it will still only be Brand that is expanded and only that column.
The bolded arguments is 2 lists that contains the Column names to expand and the new names of the columns – the last argument is optional so we can actually skip that if we want the original names – https://docs.microsoft.com/en-us/powerquery-m/table-expandtablecolumn
Now by changing the formula to this
= Table.ExpandTableColumn(#”Merged Queries”, “ProductAttributes”,List.RemoveItems(Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0}), {“ProductKey”})
)
We can make the table dynamically expand when adding new columns in the table ProductAttributes
We get the new column included as well
The magic formula does this
Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0})
Will return a list of column names from the step before
expansion (note I use the step name and column name) – and I use the {0} to extract the column names only form the first row – otherwise the formula will fail.
But as we cannot have the same column names twice (i.e. ProductKey needs to go away) so we need to use the List.RemoveItems functions
List.RemoveItems(Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0}), {“ProductKey”})
Thereby removing the ProductKey Item in the list
And this means that when we get more columns in the table “ProductAttributes” table they will automatically be included in the expanded columns
Hope this can help you power queries even more dynamic.
Here is an example file – Link
Power Query On !
Unfortunately I wasn’t able to participate in the Microsoft Business Application Summit this year – but luckily we can watch all the session via https://community.powerbi.com/t5/MBAS-Gallery/bd-p/MBAS_Gallery
But that would mean I had to leave Power BI Desktop in order to search and watch the videos – and the website also made it hard to see the distribution of sessions between categories.
So, I created this –
And to watch the video from within PowerBI I created a drill through page where I used the PowerApp visual to be able to show the
As none of the Microsoft standard visuals can play videos from within a report – I created a power App to show the video based on the selected video link.
If you want to embed this huge resource of learning material in your own tenant you can download the elements from here
The Desktop file – link
The Power App – link
If you are interested in learning how I scraped the web page for all the relevant data – check out these functions to extract data from pages using CSS query capabilities in the power query function Html.Table
Highly inspired by this blog post by Chris Webb – https://blog.crossjoin.co.uk/2018/08/30/power-bi-extract-urls-web-page/
One of the things you should know when working with PowerQuery is that you can get a list of all functions in M by adding a blank query and use the #shared expression to get all the functions.
This can be turned into a table by clicking the “Into Table” button on the ribbon and use the filter to find the function you want to learn about
Now when you click the cell “Value” for a given function the documentation of the function will appear below.
This is almost identical of what you can find online using the https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference – but you won’t have to leave the Query editor
If you use the formula bar and
And complete it without parenthesis
You will get the documentation as well –
This is useful when you write your own Power Query formulas and need more info about the function that the intellisense gives you.
Power ON!
Power Query has a lot of built in functions, but it doesn’t have a function that exactly matches the VLOOKUP in Excel – but by using the M language and other M functions we can certainly replicate some of the VLOOKUP functionality and make an even more flexible lookup function.
Now the example data is like this
In Excel we would lookup the price for at specific productkey by using this formula
– in this case ProductKey 1 with a price of 100.
In order to replicate this in Power Query we can use the function List.PositionOf
So I add a new blank query
And then use the function List.PositionOf – with the following arguments
List – Is the column ProductKey from my lookuptable Products – refer to like this Products[ProductKey]
Value – Is the value to look in this case the value 1
Occurrence – Is set to 0 to only return one value
This will return the position of the value in the list – sort of like using the MATCH function in Excel
Now to return the price – we can use this result to lookup the price like this
= Products[Price]{List.PositionOf(Products[ProductKey], 1, 0)}
And we get 100 returned which is the price of productkey 1.
The formula is structured like this
=NameOfTheTable[NameOfTheColumnToReturnTheValueOf]{PositionReturnedByListPositionOf}
But we why not change it into a function in PowerQuery so we use the function on all rows in a table or on any table.
The function can be created like this
The code
(lookupValue as any, lookupTable as table, lookupColumnName as text, returnColumnValue as text) => let // lookupTable= Products, // lookupColumnName = "ProductKey", // returnColumnValue = "Price", // lookupValue = 1, colLookup = Table.Column(lookupTable, lookupColumnName), colToReturn = Table.Column(lookupTable, returnColumnValue), lookup = List.PositionOf(colLookup, lookupValue, 0), Result = if lookup >=0 then colToReturn{lookup} else "Not found" in Result
The function takes 4 arguments –
lookupValue – The value to find – can be any type
lookupTable – The Table/Query to lookup in
lookupColumnName – The name of the column to lookup the value in
returnColumnValue – The name of the column from the table to return
The colLookup is a variable that uses the function Table.Column to return a list of values in the lookup column.
The colToReturn is a variable that uses the function Table.Column to return a list of values from the values you want to return column.
The lookup variable uses the List.PositionOf to find the position/index of the search value in the lookup column.
Result will use an if statement to test whether a position is found and if so returns the value at the position in the colToReturn list – other wise returns the text “Not Found”.
After this we can use the function in other tables to lookup the for instance the Product price like this – by added a invoke Custom Function Column
OBS – I haven’t tried this on a large table so be aware of any performance issues.
Hope you find this useful – and happy Querying
Here is a link to an example file – Link
I follow EU Eurostat on twitter (link – https://twitter.com/EU_Eurostat ) and often see a lot of interesting facts and infographics like this one.
And I have for a long time wanted to see if I could use the webservices that Eurostat also provides (link – https://ec.europa.eu/eurostat/data/web-services) to import the data directly into Power BI.
So here is a guide on how you can do it – and the example will try to extract the data for the Orange production Infographic.
There is a LOT of different datasets in Eurostat and this guide should work on most of them – you just need to find the dataset (https://ec.europa.eu/eurostat/data/database) in the catalogue.
The REST request we need to construct is defined like this
So, we need to find the datasetCode and specify the filters.
You can find the dataset code by browsing the data catalogue – and the dataset code is stated at the end.
If you need specific items the data explorer you need to specify the code of the items in the request and the Dataexplorer is a good way to find these.
Using the codes we have found we can now use the Query builder to construct our query (click on the picture to try it out)
So after entering the dataset code we get the option to specify the filter and select measures under strucpro
Notice that I have selected Yes to Exclude EU aggregates
The query option will vary from dataset to dataset but the principles are the same.
Clicking the “Generate query filter” will give you
And you can copy the dataset code to clipboard
apro_cpsh1?filterNonGeo=1&precision=1&crops=T1000&strucpro=AR&strucpro=PR_HU_EU&time=2014&time=2015&time=2016&time=2017
Now we have the filter part and this can of course be parametrized in your Power Query.
And we must add
http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/
before so the full web query is
In order to get the data into Power BI we choose get data and select the Web connector
And press OK
This will return the following to the query editor
We get a JSON response returned from the Web query and this has to be transformed into a table – and in order to get that we need to understand what and how the data is returned.
When we use the query in the browser you can see that its not a normal structured JSON document.
And what we want is a table with the following fields – Country, Year, Area, Production
I start by renaming the query to “QueryResult” and disable the load – and the following query will use this as a reference
So lets create the Geo dimension
By clicking the Record on the dimension field
Drill down to geo
Down to category
And to label
Turn it into a table
And then add an index column starting from 0 and rename the columns
I then do the same for the other dimensions and end up with the following queries
Now in order to get the values we need to do a little more
The web query returns a one long list of values when converted into a table.
Values contains all the “values” in the grid when
Each cell in the table is referred by a running index starting from 0 to Facts multiplied by Geo by Time.
So when we have 2 facts, 38 countries and 4 years will give us 2 * 39 * 4 = 304 – this should be the number of rows in our Values table.
But when we look at the Values before we do anything we have only 282 rows.
The missing Values in because cells with missing values (represented by a : or :z ) is located in the Status field in the QueryResult.
So we have to add the missing rows from this Status – this gives us the missing 22 cells (304-282)
And we then convert these Values to null
In our values Query we want to append these rows to our table – and we can do this by modifying the step – by modifying the expression to
= Record.ToTable(value) & MissingRows
And we rename and change the Name column to Index and change the data type to an integer.
The index column is our number reference to each cell in the table/matrix from EuroStat.
Next step is to calculate the keys for time, geo and Facts.
To calculate the TimeKey we add a column
And divide it by the number to time periods
This gives a step with the following expression
= Table.AddColumn(#”Added Index”, “TimeKey”, each Number.Mod([Index], 4), type number)
And after click OK we can make it more dynamic by modifying the 4 to the number of rows in
= Table.AddColumn(#”Added Index”, “TimeKey”, each Number.Mod([Index], Table.RowCount(Time)), type number)
And now we have the TimeKey for each row.
To add the FactKey we add another calculated column
= Table.AddColumn(#”Inserted Modulo”, “FactKey”, each Number.IntegerDivide([Index], Table.RowCount(Geo)*Table.RowCount(Time)), Int64.Type)
This will give us the factkey and we can see it shifts when we reach row 152 – time count = 4 and geo count = 38 = 152
Now we need the final key column is the geoKey
= Table.AddColumn(#”Inserted Integer-Division”, “GeoKey”, each Number.IntegerDivide([Index], Table.RowCount(Time) ) – [FactKey]*Table.RowCount(Geo), Int64.Type)
And we are now ready to load the data into our data model.
After closing the query editor we get the tables returned to Power BI Desktop and we can finalize the datamodel
And create the relationships between our tables.
And create some measures
And start to visualize the Oranges in Europe
One interesting fact is that the Orange trees are highly productive in Albania.
When 2018 figures is available in EuroStat – we just modify our query to
and refresh our model.
You can download a copy of the pbix file – here.
Did you like it then give the post a like or a comment – let me know what you think – hope you find it useful.
And at least take a spin around Eurostat to what interesting facts that you can find in the many datasets that are provided to you for free.
