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"
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)
