Merge Duplicate Row Data into Multiple Columns

[jamobe]

Greetings fellow excel enthusiasts.

I'm really stuck with this problem, as in the below example I am trying to get my data onto 1 row. Have followed a few power query tutorials on here and on youtube but cannot get it to work as some of my SKUs are text and numbers. So when editing the Text.Combine code, it still resulted in errors.

Basically trying to get the data in the 1st table below to be like the 2nd table with multiple columns for their images. Even just getting the images merged with a delimiter would be good. Ideally I would like to keep the Sort order the same also. Any help would be much appreciated. Thanks

SKU	Image	Is Main	Sort Order
445511​	Image A	TRUE​	0​
7226694​	Image A	TRUE​	0​
7226694​	Image B	FALSE​	1​
7226694​	Image C	FALSE​	2​
7226694​	Image D	FALSE​	3​
7226694​	Image E	FALSE​	4​
7228970​	Image A	TRUE​	0​
7229037​	Image A	TRUE​	0​
7229037​	Image B	FALSE​	1​
7229037​	Image C	FALSE​	2​

SKU	Image 1	Image 2	Image 3	Image 4	Image 5
445511​	Image A
7226694​	Image A	Image B	Image C	Image D	Image E
7228970​	Image A
7229037​	Image A	Image B	Image C

 

[lrobbo314]

How about this?

Book1
	A	B	C	D	E	F
1	SKU	Image.1	Image.2	Image.3	Image.4	Image.5
2	445511	Image A
3	7226694	Image A	Image B	Image C	Image D	Image E
4	7228970	Image A
5	7229037	Image A	Image B	Image C
Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"SKU"}, {{"Image", each _, type table [SKU=text, Image=text, Is Main=text, Sort Order=any]}}),
    Combine = Table.TransformColumns(Group,{{"Image", each Text.Combine(_[Image],",")}}),
    Split = Table.SplitColumn(Combine, "Image", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Image.1", "Image.2", "Image.3", "Image.4", "Image.5"})
in
    Split

 

[jamobe]

Hi

Thanks, I actually have just been reading a similar response you left on another thread, also using a transpose formula. I cannot seem to get that code above to work, I am copy and pasting it into the formula part of power query. Not sure what to do with the code after that tbh

 

[lrobbo314]

Load the data into Power Query, then click on the 'Advanced Editor' button and paste the code there. Also, make sure that your table name matches the table name in the code. I.E. make sure the table is actually named Table1.

 

[jamobe]

Sorry, I never placed the = at start! Works perfectly, thanks very much. The code looks the exact same way as the methods I was going by on the tutorials, but I just couldn't for the life of me get it to work

 

[lrobbo314]

The code before works on the example, but this version will be more dynamic. For example, if you added another row for SKU 7226694 with 'Image F', it won't show up. This code below will accommodate more entries.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"SKU"}, {{"Image", each _, type table [SKU=text, Image=text, Is Main=text, Sort Order=any]}}),
    Combine = Table.TransformColumns(Group,{{"Image", each Text.Combine(_[Image],",")}}),
    Split = Table.SplitColumn(Combine, "Image", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
        List.Generate(()=>1, each _ < List.Count(List.Distinct(Source[Image]))+1, each _ + 1, each "Image " & Text.From(_))
    )
in
    Split

 

[jamobe]

The code before works on the example, but this version will be more dynamic. For example, if you added another row for SKU 7226694 with 'Image F', it won't show up. This code below will accommodate more entries.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"SKU"}, {{"Image", each _, type table [SKU=text, Image=text, Is Main=text, Sort Order=any]}}),
    Combine = Table.TransformColumns(Group,{{"Image", each Text.Combine(_[Image],",")}}),
    Split = Table.SplitColumn(Combine, "Image", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
        List.Generate(()=>1, each _ < List.Count(List.Distinct(Source[Image]))+1, each _ + 1, each "Image " & Text.From(_))
    )
in
    Split

That's great. thanks for your help, much appreciated