#PowerQuery – Dynamically reference a table via a Parameter

The other day I had a fun challenge from my co-worker – Søren Faurum – that wanted to change the refence to a column name from one table to another table with the same column name.

OBS – The solution is not supported in the Power BI Service.

In his case it was

Let
   x= List.Distinct(tableName1[StoreKey])
in
   x

And he wanted TableName1 to be a value of a parameter.

Here is an example (data is from AdventureWorks) – in my model I have two tables – DimReseller and DimCustomer – both have a GeographyKey

A parameter called TableName should now be used in a query to retrieve either the unique list of values of the key from either DimReseller or DimCustomer.

If we just use

List.Distinct(TableName[GeographyKey])

We do get an expression error as the parameter is a text value and not a table that has fields.

Then I thought we could the function Expression.Evaluate() – link to documentation –

But no – apparently, we can’t reference a Table name like this.

#shared to the rescue 🆘

Then I thought why not use the #shared function – as I knew that #shared will list all the functions, tables, parameters etc. that are available in the current pbix file – including all loaded or not loaded tables.

Then we can turn the record into a table and filter it based on a parameter

This will give us a table with one record.

Where the Value column will contain the table we want to be dynamic

Then by adding the following to the formula

• {0} to get the first row/record
• [Value] to retrieve the Table in the record
• And [GeographyKey]

We can get all the Geography Keys in the table

And by putting all of this into the argument of the List.Distinct function

= List.Distinct(Table.SelectRows(#"Converted to Table", each [Name] = TableName){0}[Value][GeographyKey])

Which returns 510 rows.

And now I can change the parameter to DimCustomer

And see it returns 336 rows

However if we publish the model to the Power BI Service it won’t work if you want to schedule refresh as #shared is not supported in the service.

So we can only use it in the desktop

Stay Querious

Converting xls files to xlsx file using #powerautomate and avoid the pitfalls in #powerquery using xls

As described in this post – https://www.ehansalytics.com/blog/2020/2/15/avoid-using-excel-xls-files-as-data-source –
there are issues to be aware off when you use xls files instead of xlsx in Power Query. See also this thread
https://social.technet.microsoft.com/Forums/en-US/41f2c8ec-1f2c-4591-ac6a-54764b2a90a7/bug-in-excelworkbookwebcontents-powerquery?forum=powerquery.

Answering the twitter started by Imke Feldmann (https://twitter.com/TheBIccountant) thread by Ruth Pozuelo (go follow her excellent youtube – channel – link) encouraged me to write this post – – as I claimed we can convert the xls files to xlsx using Power Automate.

So here is a guide on how to do it

Convert xls files to xlsx

In the scenario I will use a trigger when an e-mail is received and use a rest API provided by https://cloudconvert.com/.

OBS – This is a paid service where you pay by the minute the conversion takes – price from $0.02 to $0.01 per minute.

First we start by selecting to build an automated flow and select the trigger “When a new email arrives (V3)”

Set the advanced options to only trigger when Attachments is included and include the attachments in the following steps in our flow.

As the next step I use an Azure Blob storage to store the file in the e-mail. When selecting the output from the previous step – power automate will automatically create an Apply to each container in which we can refer to each attachment in the mail.

In the Create Blob Action I connect to a blob storage and load the attachment into a preload folder

Now add a step where we create an HTTP request within the Apply to each container

In order to use the cloud convert REST API we need first to create a Process and use the process ID to create the conversion – documentation here

In the body property you specify your own APIKey and tell the process you want to do is a conversion from xls format to xlsx.

Next – add another HTTP request

We use a POST request again

And in the URI we use the result from the previous step as it returns a unique address to the process id from cloud convert

In the Body property we specify where

{

"input": {

"azureblob": {

"storageaccount": "<NAMEOFBLOBACCOUNT>",

"storageaccesskey": "<AccountKey>",

"container": "preload"

}

},

"file": @{body('Create_blob')?['Name']},

"timeout": 10,

"output": {

"azureblob": {

"storageaccount": "<NAMEOFBLOBACCOUNT>",

"storageaccesskey": "<AccountKey>",

"container": "converted"

}

},

"outputformat": "xlsx"

}

Here is the final overview of the steps needed.

Now send an email to the inbox you have connected your trigger to run.

And in our preload folder we can see the files

And in the converted folder we have the converted xlsx files

Hope this can help you converting your xls files to xlsx.

This will also make it much easier if you want to be able to setup

Power On!

#PowerQuery – Using SQL scalar value functions in PowerQuery

Currently I am working with a project where we extract data from a SQL server – some of the business logic is built into scalar value functions (documentation).

Now the magic of PowerQuery enables us to reuse these functions within PowerQuery and Query Folding is supported – more about this at the end of this post.

Let’s step through how to do this

I will use the famous AdventureWorksDW2014 database.

In my database I have added a function called IsoweekwithYear

This function uses a date a parameter and returns an Integer representing the year and the week. In this example it returns 202018 as 28 april 2020 is in iso week 18 of 2020.

In the Power BI Desktop we can now connect to the SQL server

And in this example I pick the DimDate table from the database

And if we scroll down you will also see the functions, we have in the database

Notice that the data preview pane will show the function and the parameters it can take.

Then I click Transform Data and the Power Query Editor will appear

By default it will Invoke the function and in this case it will return Null

Notice the Applied Steps in the Query Settings pane

If you remove the last step where the function is invoked

You will see it as a function that has a parameter called @DATE which is optional.

Now let’s use the function

In the Query DimDate we can the call the function – as we now we need to call the function using the column FullDateAlternateKey as the parameter I select this column first and then click Invoke Custom Function via the Add Column section in the ribbon.

We can then choose the Function Query IsoweekWithYear and use the column name as the parameter value

This will add a new column at the end of my query

Containing the ISOWeek – yuppieee 🙂

Is Queryfolding supported ?

It sure is

As we can see as we can select “View Native Query” and see that the SQL statement is added.

This means that the server does the work instead of the M-engine.

What’s next

I must admit that I wasn’t aware of this possibility before – and I feel like it opens up a lot of scenarios to reuse already built and tested functions that your database developers have built.

Let me know if you find this useful as well by leaving a comment or give the post a like

Happy querying !!!

#PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

Today I discovered a way to easily copy all queries (including the groups) from a PBIX file to Excel without entering the power query editor in Excel.

In my case I needed to copy the data returned by a query and find it some much easier to get the data using Excel instead of generating a csv file from a visual in Power BI Desktop and importing that into Excel.

My PBIX contains some queries to extract weather data from different stations in Sweden.

These are grouped to create a logic structure of the queries.

Select all queries and copy them.

Now open a blank workbook and click the Queries & connections in the Data tab in the ribbon

And the Queries & Connections panels opens

Now right click in the panel

And select Paste – and the queries, parameters and load settings for the queries are also included.

For the table you want to load you now need to specify whether you want to load to the table to the datamodel or to a worksheet – default will be worksheet.

If you collapse all your groups in the panel and select all of them you can also copy them

And paste them into the Power Query editor in the Power BI Desktop

And after paste

Hope you this can help you too and stay Queryious

#PowerQuery – Avoid an extra step by not clicking OK

Just wanted to share a small tip in PowerQuery where an extra step is added to your queries even though you might not want it.

In this example I have imported the FactInternetSales from the AdventureWorks database and have filtered it with orderdatekey after a specific date.

Now I just want to check the different product keys and I click the dropdown next to the field name.

If you do not specify a filter but just click OK (the default button) you will get an extra step inserted in the Applied Steps

= Table.SelectRows(#”Filtered Rows”, each true)

Which just means that all rows should be returned from the previous step.

If you press Cancel instead the step won’t be added.

So, keep your queries nice and clean and click Cancel instead of OK and stay querious 🙂

PS – and remember to rename the steps to keep them as explanatory as possible.

Quick #PowerQuery trick – Get duration days between two dates

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 –

#PowerQuery – Calculate the ISO date from year and a week number

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

#PowerQuery – Control the expand columns so it includes new columns

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.

How can we make this dynamic using PowerQuery

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 !

#powerbi Report to browse and watch sessions from #mbas 2019 using the #powerapp visual

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/

How to get help for any function in #PowerQuery

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

But did you know

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!