Dynamic Power Query Cross Tab Reports - Pivot n Rows to n Columns 2 Ways! - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Rico
I really enjoy these PQ ones, and it's good to give Phil a run out now and again!
After the Grouped Rows function:
#-Added Custom- = Table.AddColumn(#-Grouped Rows-, -Count-, each List.Count([Grouped][Department])),
#-Added Custom1- = List.Accumulate(-1..List.Max(#-Added Custom-[Count])-,#-Added Custom-,(state, current) => Table.AddColumn(state,-Department- & Number.ToText(current), each [Grouped][Department]-current-1-)),
#-Replaced Errors- = Table.ReplaceErrorValues(#-Added Custom1-, List.Transform(Table.ColumnNames(#-Added Custom1-), each -_, ---))
in
#-Replaced Errors-
It'd be interesting to see the relative speed of these different options. I couldn't work out how to get the List.Max of the List.Count in a single step, although I'm sure I could have managed with a bit of time!
reply
I really enjoy these PQ ones, and it's good to give Phil a run out now and again!
After the Grouped Rows function:
#-Added Custom- = Table.AddColumn(#-Grouped Rows-, -Count-, each List.Count([Grouped][Department])),
#-Added Custom1- = List.Accumulate(-1..List.Max(#-Added Custom-[Count])-,#-Added Custom-,(state, current) => Table.AddColumn(state,-Department- & Number.ToText(current), each [Grouped][Department]-current-1-)),
#-Replaced Errors- = Table.ReplaceErrorValues(#-Added Custom1-, List.Transform(Table.ColumnNames(#-Added Custom1-), each -_, ---))
in
#-Replaced Errors-
It'd be interesting to see the relative speed of these different options. I couldn't work out how to get the List.Max of the List.Count in a single step, although I'm sure I could have managed with a bit of time!
reply
Simon
Mynda - you know I'm a fan but when your preferred tool is a hammer, every problem looks like a nail.
This is a job for a a -helper- Y/N column to say whether or not that department exists in the location. Then use a DAX =CONCATENATEX() function in a Power Pivot. This does away with they unhelpful column headers of Column 1, 2, etc and show what you have where:
The DAX measure is =CONCATENATEX(tblLocDept,[Loc'nHasDept]) where I named the table tblLocDept and the helper field [Loc'nHasDept].
The beauty of this approach is that you could include columns for Dept Managers, Dept Inception Dates, owt you like really and CONCATENATEX them.
Maybe I'm missing the point, or perhaps my preferred tool is a screwdriver...
reply
Mynda - you know I'm a fan but when your preferred tool is a hammer, every problem looks like a nail.
This is a job for a a -helper- Y/N column to say whether or not that department exists in the location. Then use a DAX =CONCATENATEX() function in a Power Pivot. This does away with they unhelpful column headers of Column 1, 2, etc and show what you have where:
The DAX measure is =CONCATENATEX(tblLocDept,[Loc'nHasDept]) where I named the table tblLocDept and the helper field [Loc'nHasDept].
The beauty of this approach is that you could include columns for Dept Managers, Dept Inception Dates, owt you like really and CONCATENATEX them.
Maybe I'm missing the point, or perhaps my preferred tool is a screwdriver...
reply
Jim
Brilliant! I don-t anticipate much use of this. Lately, I use dynamic arrays to build cross tab reports when I don-t need/want to use pivot tables or Power Pivot. I love LET!
Idea for determining # of columns:
Add a step that calculates a variable as the count of unique Department values in the Source table, then use that value in the transform step that requires column names or # of columns. I think that would be simpler & easier than building the comma-delimited text string, then counting # of commas.
reply
Brilliant! I don-t anticipate much use of this. Lately, I use dynamic arrays to build cross tab reports when I don-t need/want to use pivot tables or Power Pivot. I love LET!
Idea for determining # of columns:
Add a step that calculates a variable as the count of unique Department values in the Source table, then use that value in the transform step that requires column names or # of columns. I think that would be simpler & easier than building the comma-delimited text string, then counting # of commas.
reply
Alan
I do consider there are some excellent skills being demonstrated.
But I don't think this is the best way to present the desired analysis.
I propose a simple pivot table by Department and Location
Then for the values, count the department
Now create a custom number format that displays any number as a tick - literally just copy this - into a custom number format
Now its super easy to visualize the correlation between Department and Location
reply
I do consider there are some excellent skills being demonstrated.
But I don't think this is the best way to present the desired analysis.
I propose a simple pivot table by Department and Location
Then for the values, count the department
Now create a custom number format that displays any number as a tick - literally just copy this - into a custom number format
Now its super easy to visualize the correlation between Department and Location
reply
Wayne
Hi Mynda and Phil. Thanks for this awesome lesson! Really appreciate knowing how to make it dynamic vs. having to rebuild the query when the data changes. Thanks for sharing the two techniques :)) Also, thanks for showing the method of building a table ref with #shared to help with the process. Thumbs up!!
reply
Hi Mynda and Phil. Thanks for this awesome lesson! Really appreciate knowing how to make it dynamic vs. having to rebuild the query when the data changes. Thanks for sharing the two techniques :)) Also, thanks for showing the method of building a table ref with #shared to help with the process. Thumbs up!!
reply
Alberto
Sensational Phil! You think and combine things in PQ in a very smart way and you make it seems so easy in the video. I like a lot this dynamic expansion. It can be used in various contexts. Thank you for sharing!
reply
Sensational Phil! You think and combine things in PQ in a very smart way and you make it seems so easy in the video. I like a lot this dynamic expansion. It can be used in various contexts. Thank you for sharing!
reply
Prem
Scheduled refresh is slow when compare to manual refresh
You guys know anyother way to do automatically using other apps
Like power shell or python
reply
Scheduled refresh is slow when compare to manual refresh
You guys know anyother way to do automatically using other apps
Like power shell or python
reply
Quidisi
BRILLIANT! BRILLIANT!! BRILLIANT!!!
So very helpful. Doesn't just tell us what to do, but tells us WHY to do it!!
True Teachers, you two are!
reply
BRILLIANT! BRILLIANT!! BRILLIANT!!!
So very helpful. Doesn't just tell us what to do, but tells us WHY to do it!!
True Teachers, you two are!
reply
John
I've only just stumbled on power query, and it's incredible!
where was this functionality back in 2010 when I was working as a data analyst!!!!!
reply
I've only just stumbled on power query, and it's incredible!
where was this functionality back in 2010 when I was working as a data analyst!!!!!
reply
Jerry
Nice. In method 2 you could have avoided counting commas by simply modifying the group-by to also do the Count as another aggregation.
reply
Nice. In method 2 you could have avoided counting commas by simply modifying the group-by to also do the Count as another aggregation.
reply
Add a review, comment
Other channel videos
