Pivot columns vs group

[k1s1]

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? 

[Jimmy801 · Accepted solution]

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

[k1s1]

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

[Jimmy801]

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

[k1s1]

Thanks - it's the strategy to use a Count column that I'm struggling to get my head round 

[Jimmy801]

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

[k1s1]

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],

[Jimmy801]

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

[Anonymous]

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