Forum Discussion
Pivot columns vs group
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
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
Hi 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
JimmyThanks - it's the strategy to use a Count column that I'm struggling to get my head round
Hello k1s1
you can also not add a count-column and just count the row of pivoted rows. 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}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Uplift", type text}}, "de-DE"), List.Sort(List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Uplift", type text}}, "de-DE")[Uplift]), Order.Ascending), "Uplift", "Uplift", List.Count) in #"Pivoted Column"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