Forum Discussion
Pivot columns vs group
Hello
I'm struggling with pivoting and grouping amd woudl greatly appreciate some help
I have a few hundred rows of data like this:
5 Ideas a "Score before" from 1-7 and an Uplift also between 1-7.
Idea | Score before | Uplift |
idea4 | 6 | 7 |
idea4 | 4 | 5 |
idea5 | 4 | 5 |
idea1 | 4 | 5 |
idea2 | 5 | 5 |
idea5 | 7 | 7 |
idea5 | 7 | 6 |
idea4 | 7 | 7 |
idea2 | 6 | 6 |
If pivot and re-order columns, like this
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns2", each ([Idea] = "idea5")),
#"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows1", {{"Uplift", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows1", {{"Uplift", type text}}, "en-GB")[Uplift]), "Uplift", "Idea", List.Count),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column1",{"Score before", "1", "2", "3", "4", "5", "6", "7"}),
I get something like this:
| Score before | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 |
| 2 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 3 | 1 | 1 | 1 | 0 | 1 | 0 | 0 |
| 4 | 0 | 2 | 2 | 10 | 9 | 6 | 0 |
| 5 | 0 | 0 | 1 | 7 | 10 | 8 | 5 |
| 6 | 1 | 0 | 1 | 6 | 13 | 24 | 21 |
| 7 | 0 | 0 | 1 | 2 | 10 | 24 | 50 |
But what I'd really like is to get the pivot arranged like this:
| Idea | Score before | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| idea5 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 |
| idea5 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| idea5 | 3 | 1 | 1 | 1 | 0 | 1 | 0 | 0 |
| idea5 | 4 | 0 | 2 | 2 | 10 | 9 | 6 | 0 |
| idea5 | 5 | 0 | 0 | 1 | 7 | 10 | 8 | 5 |
| idea5 | 6 | 1 | 0 | 1 | 6 | 13 | 24 | 21 |
| idea5 | 7 | 0 | 0 | 1 | 2 | 10 | 24 | 50 |
| idea4 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| idea4 | 2 | 0 | 1 | 0 | 0 | 2 | 0 | 1 |
| idea4 | 3 | 0 | 0 | 0 | 1 | 2 | 1 | 1 |
| idea4 | 4 | 0 | 1 | 1 | 14 | 8 | 4 | 4 |
| idea4 | 5 | 0 | 0 | 0 | 3 | 12 | 10 | 9 |
| idea4 | 6 | 0 | 0 | 0 | 3 | 15 | 30 | 15 |
| idea4 | 7 | 0 | 0 | 0 | 6 | 5 | 17 | 45 |
i.e. in the Table above - for a given Idea, idea5, if the score before was 4, the number of times it was rated 5 in he Uplift was 9
Is some kind of Grouping the answer instead of using the pivot function?
Hello k1s1
not able to follow you. Could you please a few lines of your dataset and what the expected result is of that. What I understood is that this "9" is a count of rows where idea5 with rating 4 and uplift 9. I'm wrong? If not try this... add a count-column with each 1. Then pivot the Uplift-column and summing the count-column. Here the code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxJTTRR0lECYXOlWB1CIkYoIsZkiRgBsSGGCKYaY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Idea = _t, #"Score before" = _t, Uplift = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Idea", type text}, {"Score before", Int64.Type}, {"Uplift", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each 1, type number), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Uplift", type text}}, "de-DE"), List.Sort(List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Uplift", type text}}, "de-DE")[Uplift]), Order.Ascending), "Uplift", "Count", List.Sum) in #"Pivoted Column"transforms this
into this
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
8 Replies
"i.e. in the Table above - for a given Idea, idea5, if the score before was 4, the number of times it was rated 5 in he Uplift was 9"
but can the 9 be obtained from the example data you provided above or does it derive from the complete table?
In this latter case you should load the table in such a way that it can be copied and explained in more detail (perhaps with some more examples) the logic to be applied)
Hello k1s1
not able to follow you. Could you please a few lines of your dataset and what the expected result is of that. What I understood is that this "9" is a count of rows where idea5 with rating 4 and uplift 9. I'm wrong? If not try this... add a count-column with each 1. Then pivot the Uplift-column and summing the count-column. Here the code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxJTTRR0lECYXOlWB1CIkYoIsZkiRgBsSGGCKYaY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Idea = _t, #"Score before" = _t, Uplift = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Idea", type text}, {"Score before", Int64.Type}, {"Uplift", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each 1, type number), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Uplift", type text}}, "de-DE"), List.Sort(List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Uplift", type text}}, "de-DE")[Uplift]), Order.Ascending), "Uplift", "Count", List.Sum) in #"Pivoted Column"transforms this
into this
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi Jimmy,
Your solution is working perfectly, thank you.
I don't understand it though.
#"Pivoted Column" = Table.Pivot( Table.TransformColumnTypes(#"Added Custom", {{"Uplift", type text}}), List.Sort( List.Distinct( Table.TransformColumnTypes( #"Added Custom", {{"Uplift", type text}} )[Uplift] ), Order.Ascending ), "Uplift", "Count", List.Sum )Would you mind explaining the code above? I can't work out what it's doing
Hello k1s1
this is basically the code by the GUI and as the pivoted column has to be text, the GUI is transforming it twice
Here the link to the description of the function so you can check out every parameter of it
https://docs.microsoft.com/en-us/powerquery-m/table-pivot
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
