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"

k1s1's avatar
k1s1
Helper I
5 years ago

Hi edhans 

 

Thanks for your offer to help, here is some randonly generated data (It doesn't contain all 50 brandnames), but hopefully sufficient

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZi7bhwxDEV/xZjahUQ9ZrZNk49YuDCQlHECA/n/ZNfDmDySmC0GA0Hr4evy8tLX6/bl/fXt29vrj+9P9diezbE0d8RtFv/j6o5yu93Pp/99btflfH99//n711PeXp6vwQdzZE56dKy348179aIaDz6sC633MDrE7n+cLuNtN1bbEHuF9erNJTiTxtyav/W3tZ2WunlLGLsckbnqK5s8KlJxx5ZOa/V8t/PpS+sp/D6sC1KNH1+MtXJ60E8MLDI/qd2yEAkN4mNPWu/deKDZ/7BeaP0Ys2du9yh2QeyKOos2cdZbjLrSI3O89e1571ZFm0VeXVtvUTjJx559qrNHRT6ANM3DZ93JNuhZwBj9nnywQGxVZtG6K+sEdccHgSsfe41BWBGvehLEjn4PoyP5oC4Xw6y7Qd8adfggWozkBph5kJSL6TTtPq3/Y9Z9OAUN6I9AnVirYvKwZlqUUkqYCmB+0q2CmMsWMS34pPrvSzhhZ1NG+d3WvS1RRzZDbpEK7wwgekdd2fykiVEHrgbtQ72Qh8EGl81rGzGezPs9Y8pIGCyIHUedsM14od23yDz8B4Gg7hJaV2uKumI8eCjzaCLgCmNFoA0U57KNM34+3zkpwCf4/h6lIumUsYpS9d287uCT4mGA+YsGKZMWsNpOYy/L2NFixLyvLFEHz8k2isC1dSAHqUZHQO0jb6JTppoceMz/R1Ezt5BSYZnubGA5zqqrecexdug4zN8WZebfNtFN7H6+DzMOH0R0JcoMuEjy5jVtHWLnlEEpsVxwoGMkyRi7Zt3m4LPfhy0SQg6qMo/RLX0Tq2XoyYLrehQdtQcyA57UGac4r+a90HVYGxFdSG4UG6qs7CatCvuhGUfBEBP7hJrso4q6L60z8yAQdJy3zn81aNYt36kXi7qDT0JNm0P5Xaioxn4f2CZu8COyzi2P26PdKuaxcy3dwyMgihmRN7+32k16wfNYjmBusiPHMs9mfIx9UFYTXbrsuBoqn2wZ1u40a67DpkbMh0ybJw24Tyy7/f3lDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brandshown1 = _t, Brandshown2 = _t, Brandshown3 = _t, Brandshown4 = _t, Brandshown5 = _t, Brandshown6 = _t, #"Score Brandshown1" = _t, #"Score Brandshown2" = _t, #"Score Brandshown3" = _t, #"Score Brandshown4" = _t, #"Score Brandshown5" = _t, #"Score Brandshown6" = _t, #"Customer Group" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brandshown1", type text}, {"Brandshown2", type text}, {"Brandshown3", type text}, {"Brandshown4", type text}, {"Brandshown5", type text}, {"Brandshown6", type text}, {"Score Brandshown1", Int64.Type}, {"Score Brandshown2", Int64.Type}, {"Score Brandshown3", Int64.Type}, {"Score Brandshown4", Int64.Type}, {"Score Brandshown5", Int64.Type}, {"Score Brandshown6", Int64.Type}, {"Customer Group", type text}})
in
    #"Changed Type"