Most efficient way to pivot

[k1s1]

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 get a distribution of scores by brand.

As shown in the picture below the structure is basically 6 columns, each one showing which of 50 brands was shown to each respondent, and another 6 columns showing the number/score that the respondent gave to that brand, plus a final column showing the customer group/segment.

The output I'm looking for is a table list each brand, and the average score given to it (by customer Group)

Can anybody think of a better way of doing it than creating 50 groups of if statements like this: 

let
    Source = Table.Buffer(#"Working Data for Charts"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Q27_1 Brand favorability - [Field-brandshown1]", "Q27_2 Brand favorability - [Field-brandshown2]", "Q27_3 Brand favorability - [Field-brandshown3]", "Q27_4 Brand favorability - [Field-brandshown4]", "Q27_5 Brand favorability - [Field-brandshown5]", "Q27_6 Brand favorability - [Field-brandshown6]", "Group", "brandshown1", "brandshown2", "brandshown3", "brandshown4", "brandshown5", "brandshown6"}),

 // Create a column to merge scores for each brand

   #"Added Brandname 01 brand fav" = Table.AddColumn(#"Removed Other Columns", "Brandname 01 ", each 
   if [brandshown1] = "Brandname 01" then [#"Q27_1 Brand favorability - [Field-brandshown1]"] else 
   if [brandshown2] = "Brandname 01" then [#"Q27_2 Brand favorability - [Field-brandshown2]"] else 
   if [brandshown3] = "Brandname 01" then [#"Q27_3 Brand favorability - [Field-brandshown3]"] else 
   if [brandshown4] = "Brandname 01" then [#"Q27_4 Brand favorability - [Field-brandshown4]"] else 
   if [brandshown5] = "Brandname 01" then [#"Q27_5 Brand favorability - [Field-brandshown5]"] else 
   if [brandshown6] = "Brandname 01" then [#"Q27_6 Brand favorability - [Field-brandshown6]"] else null),


   #"Added Brandname 02 brand fav" = Table.AddColumn(#"Added Brandname 01 brand fav", "Brandname 02 ", each 
   if [brandshown1] = "Brandname 02" then [#"Q27_1 Brand favorability - [Field-brandshown1]"] else 
   if [brandshown2] = "Brandname 02" then [#"Q27_2 Brand favorability - [Field-brandshown2]"] else 
   if [brandshown3] = "Brandname 02" then [#"Q27_3 Brand favorability - [Field-brandshown3]"] else 
   if [brandshown4] = "Brandname 02" then [#"Q27_4 Brand favorability - [Field-brandshown4]"] else 
   if [brandshown5] = "Brandname 02" then [#"Q27_5 Brand favorability - [Field-brandshown5]"] else 
   if [brandshown6] = "Brandname 02" then [#"Q27_6 Brand favorability - [Field-brandshown6]"] else null),
   
   // THEN the same for Brandnames 03-49, THEN...
   

   #"Added Brandname 50 brand fav" = Table.AddColumn(#"Added Brandname 50 brand fav", "Brandname 50 ", each 
   if [brandshown1] = "Brandname 03" then [#"Q27_1 Brand favorability - [Field-brandshown1]"] else 
   if [brandshown2] = "Brandname 03" then [#"Q27_2 Brand favorability - [Field-brandshown2]"] else 
   if [brandshown3] = "Brandname 03" then [#"Q27_3 Brand favorability - [Field-brandshown3]"] else 
   if [brandshown4] = "Brandname 03" then [#"Q27_4 Brand favorability - [Field-brandshown4]"] else 
   if [brandshown5] = "Brandname 03" then [#"Q27_5 Brand favorability - [Field-brandshown5]"] else 
   if [brandshown6] = "Brandname 03" then [#"Q27_6 Brand favorability - [Field-brandshown6]"] else null),
   
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Brandname 03 brand fav",{"Brandname 01 ", "Brandname 02 ", "Brandname 50 ", "Group"}),

 // Create pivot columns  

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns1", {"Group"}, "Brand", "Value"),
    
	#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "")),

    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
	
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Group]), "Group", "Index", List.Count)

  
in
    #"Pivoted Column"

[k1s1]

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"

[CNENFRNL · Accepted solution]

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]

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)}
						)
		}
	}
),

[k1s1]

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

[edhans]

I'm sure we could tinker with this, but need data. Cannot use images. Images are great for expected output, but I'm not keying all of that in.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.