Forum Discussion
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...
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"
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"
Would you mind explaining the Group step please? I can pick out of it that you've chopped the Score suffix off the end, with the List.RemoveLastN but really struggling with the rest
#"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)}
)
}
}
),