Forum Discussion

k1s1's avatar
k1s1
Helper I
5 years ago
Solved

Most efficient way to pivot

Hello,   I've got some survey data where 6 out of 50 or so brands were shown to a given respondent who gave each brand they saw a score from (1-7.) I'm looking to create a calculation so that I can...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZGxDsMwCER/pfKcoZA4rse2nxFlqJQ5Q/9/aJAOGyM6WL5E78AH25Ze38953DhNULkpulTGWeVv2ifliTrmpFpmZ1kb9mhKGPli8E/b4t4wHqwVMDvD0t/BQwjtIsA7NJSmFhSWUxw/R5nFQGjgI9chaFcZKXwCE9lKVM+4rSP/6WBjxzMqY5iC+ZBfdPQiHWbFHa/ZDKmiAwVbMFjPoNV1z5dh/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"brand 1st" = _t, #"brand 2nd" = _t, #"brand 3rd" = _t, #"Score 1st" = _t, #"Score 2nd" = _t, #"Score 3rd" = _t, #"Customer Group" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"brand 1st", type text}, {"brand 2nd", type text}, {"brand 3rd", type text}, {"Score 1st", Int64.Type}, {"Score 2nd", Int64.Type}, {"Score 3rd", Int64.Type}, {"Customer Group", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Group"}, {{"ar", each Table.Group(Table.FromColumns(List.Transform(List.Split(List.RemoveLastN(Table.ToColumns(_),1),3), List.Combine), {"Brand", "Score"}), "Brand", {"Avg", each Number.Round(List.Average([Score]),2)})}}),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Brand", "Avg"}, {"Brand", "Avg"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded ar", List.Distinct(#"Expanded ar"[#"Customer Group"]), "Customer Group", "Avg")
in
    #"Pivoted Column"

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZGxDsMwCER/pfKcoZA4rse2nxFlqJQ5Q/9/aJAOGyM6WL5E78AH25Ze38953DhNULkpulTGWeVv2ifliTrmpFpmZ1kb9mhKGPli8E/b4t4wHqwVMDvD0t/BQwjtIsA7NJSmFhSWUxw/R5nFQGjgI9chaFcZKXwCE9lKVM+4rSP/6WBjxzMqY5iC+ZBfdPQiHWbFHa/ZDKmiAwVbMFjPoNV1z5dh/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"brand 1st" = _t, #"brand 2nd" = _t, #"brand 3rd" = _t, #"Score 1st" = _t, #"Score 2nd" = _t, #"Score 3rd" = _t, #"Customer Group" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"brand 1st", type text}, {"brand 2nd", type text}, {"brand 3rd", type text}, {"Score 1st", Int64.Type}, {"Score 2nd", Int64.Type}, {"Score 3rd", Int64.Type}, {"Customer Group", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Group"}, {{"ar", each Table.Group(Table.FromColumns(List.Transform(List.Split(List.RemoveLastN(Table.ToColumns(_),1),3), List.Combine), {"Brand", "Score"}), "Brand", {"Avg", each Number.Round(List.Average([Score]),2)})}}),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Brand", "Avg"}, {"Brand", "Avg"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded ar", List.Distinct(#"Expanded ar"[#"Customer Group"]), "Customer Group", "Avg")
in
    #"Pivoted Column"

k1s1's avatar
k1s1
Helper I
5 years ago

Many thanks - in the time it took me to create a sample, you made a solution!.  Going through it now to see if I understand it