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
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
Thanks,
When I tried that with some rows where the idea wasn't just idea4, I didn't get the expected results (ie 1 row per idea):
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]),
#"Appended Query" = Table.Combine({Source, Table}),
#"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"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"
It ended up like this instead:
I obviously have a conceptual misunderstanding of how this works.
Looking at the link you kindly directed me to, I can see the example says:
Syntax
Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table
Table.Pivot(
Table.FromRecords({
[key = "x", attribute = "a", value = 1],
[key = "x", attribute = "c", value = 3],
[key = "y", attribute = "a", value = 2],
[key = "y", attribute = "b", value = 4]
}),
{"a", "b", "c"},
"attribute",
"value"
)
...but I'm struggling to relate that to my data.
I think my "key"s are idea1...idea5, and "attribute"s are the scores: 1-7. Is that right?
...but I don't see anything in this line:
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Uplift", type text}}), List.Sort(List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Uplift", type text}}, )[Uplift]), Order.Ascending), "Uplift", "Uplift", List.Count)...which resembles the key, attribute, value statements in the example, unless the its where you have sepcified the "Uplift" column. Is that in fact the 'key"? But then the next argument in your code is also referencing the the "Uplift" column.
I'm confused because I'm new to Power Query and having difficulty relating what happens using the GUI & the resulting M code generated
Does that mean in my case I should for each structure it with
Table.Pivot( Table.FromRecords({
[key = "idea1", attribute = "1", value = 1],
Hello k1s1
you are going in the wrong direction. The examples of the documentation is just to create a table with the function Table.FromRecords. In you scenario this parameter is filled with your real table.
In the 3rd and 4th parameter i refer both to Uplift-column (Attribute and value), because in your scenario we don't have any column we can sum etc. Therefore the 5th parameter is to count the rows and not sum them.
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