Power Pivot
Power Query
Power BI
December 8, 2020
Hello I was just looking at your newsletter on showing items in PivotTables with no data and video here, and wondered if anyone knows a way to do the same when pivoting data with Power Query.
Suppose I have a table with data like this below which could have scores between 1-7
Idea | Score Before | Score After |
Idea 1 | 1 | 2 |
Idea 1 | 1 | 2 |
Idea 1 | 5 | 3 |
Idea 1 | 5 | 7 |
Idea 1 | 3 | 4 |
Idea 1 | 3 | 6 |
Idea 1 | 2 | 4 |
Idea 1 | 4 | 4 |
Idea 1 | 4 | 4 |
Idea 2 | 3 | 1 |
Idea 2 | 4 | 2 |
...and wanted to count the number of times when a "before" and after "score" change for each permutation. For the data above, I can produces a table like this, using the pivot approach I've taken with the M code at the bottom:
Idea | Score Before | 1 | 2 | 3 | 4 | 6 | 7 |
Idea 1 | 1 | 2 | |||||
Idea 1 | 2 | 1 | |||||
Idea 1 | 3 | 1 | 1 | ||||
Idea 1 | 4 | 2 | |||||
Idea 1 | 5 | 1 | 1 | ||||
Idea 2 | 3 | 1 | |||||
Idea 2 | 4 | 1 |
...but as you can see it doesn't include score "5" in the columns or 6 & 7 in the rows (for Idea 1). I'd like to make a table which includes all permutations 1-7. Is there a simple way to do this?
I've generated the above example using this code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxJTVQwVNJRAmEjpVgd/EKmQGyMKWSOKmQMxCaYQmaoQkaYqkwIChlBzTJEFTKBODUWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Idea = _t, #"Score Before" = _t, #"Score After" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Idea", type text}, {"Score Before", Int64.Type}, {"Score After", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each 1, type number),
#"Pivoted Column" = Table.Pivot(
Table.TransformColumnTypes(#"Added Custom", {{"Score After", type text}}),
List.Sort(
List.Distinct(
Table.TransformColumnTypes(
#"Added Custom", {{"Score After", type text}}
)[Score After]
), Order.Ascending
),
"Score After", "Index", List.Sum
)
in
#"Pivoted Column"
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi,
PQ will automatically detect column names from the Score After column values, using List.Distinct.
You can replace this code from #"Pivoted Column" step:
List.Sort(
List.Distinct(
Table.TransformColumnTypes(
#"Added Custom", {{"Score After", type text}}
)[Score After]
), Order.Ascending
)
with your custom list of columns:
List.Transform({1..7},Text.From)
1 Guest(s)