cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k1s1
Helper I
Helper I

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

Pivot example.jpg

 

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"

 

 

 

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

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"

Untitled.png

View solution in original post

5 REPLIES 5
k1s1
Helper I
Helper I

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
Super User III
Super User III

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"

Untitled.png

View solution in original post

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

 

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
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors