This week I watched the video from Guy in A cube with Patrick and Alex Powers – You thought DAX? Alex shows you Dynamic M-M-Magic! – YouTube.
And one trick I noticed Alex used was something I didn’t know about Power Query and had annoyed me for a long time.
As you might have seen in my previous posts I like to make the queries as short as possible – and this tip will help me do that even better than my previous tip – How to avoid a rename step in #PowerQuery when you use Table.FromList – #PowerBI – Erik Svensen – Blog about Power BI, Power Apps, Power Query (wordpress.com)
Let’s imagine I have a query with a column called Reportid – and I want to create another query with the unique values from the column.
Normally I would create a new blank query and than reference the column with – queryName[ColumnName]
And then we have a new query that contains a list.
I want a table, so I use the “To Table” in the ribbon or the shortcut menu
Click Ok in the dialog
And a new step is added and I end up with a table
THE TRICK –
Instead use double square brackets and you will not only get a table, but the column name is also as I want it. – Query[[ColumnName]]
That is awesome – hope you find it useful too
3 thoughts on “#PowerQuery – Convert a referenced query column to a table without Table.FromList() – Magic trick”
You can also combine several columns in a new table and set the order of the columns:
= Query[[Column5], [Column1], [Column3]]
Pingback: Converting a Referenced Query Column to a Table – Curated SQL