Transpose data from duplicate rows to columns using Power Query

[eduzs]

Hello, is it possible to get the result below using Power Query? How can I do this? Thanks!

The source table has duplicate rows (name), I need to keep only duplicate rows by name and transpose the rows (Car) to columns, like this:

Data:

NAME	CAR
A	X
A	Y
B	S
C	W
C	Z

Result:

NAME	CAR1	CAR2
A	X	Y
C	W	Z

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl1 = Table.Group(Source, {"NAME"}, {{"CAR", each _[CAR]}}),
    tbl2 = Table.SelectRows(tbl1, each List.Count(_[CAR])> 1),
    maxcars = List.Accumulate(tbl2[CAR],0,(s,c)=> let lc = List.Count(c) in if lc>s then lc else s),
    ColList = List.Accumulate({1..maxcars}, {}, (s,c)=> s & {"CAR." & Text.From(c)}),
    delim = Character.FromNumber(999),
    tbl4 = Table.TransformColumns(tbl2, {"CAR", each Text.Combine(List.Transform(_, Text.From), delim), type text}),
    tbl5 = Table.SplitColumn(tbl4, "CAR", Splitter.SplitTextByDelimiter(delim, QuoteStyle.Csv), ColList),
    Result = if Table.ColumnCount(tbl5) > 1 then tbl5 else "No multi-car found"
in
    Result

 

[eduzs]

Works fine! Thanks!

 

[eduzs]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl1 = Table.Group(Source, {"NAME"}, {{"CAR", each _[CAR]}}),
    tbl2 = Table.SelectRows(tbl1, each List.Count(_[CAR])> 1),
    maxcars = List.Accumulate(tbl2[CAR],0,(s,c)=> let lc = List.Count(c) in if lc>s then lc else s),
    ColList = List.Accumulate({1..maxcars}, {}, (s,c)=> s & {"CAR." & Text.From(c)}),
    delim = Character.FromNumber(999),
    tbl4 = Table.TransformColumns(tbl2, {"CAR", each Text.Combine(List.Transform(_, Text.From), delim), type text}),
    tbl5 = Table.SplitColumn(tbl4, "CAR", Splitter.SplitTextByDelimiter(delim, QuoteStyle.Csv), ColList),
    Result = if Table.ColumnCount(tbl5) > 1 then tbl5 else "No multi-car found"
in
    Result

Hi, this code works fine! But I need to adapt to use with a total of five columns (CAR,C2,C3,C4,C5).
Is there a way to do this based on your code? Especially if possible a way that works for any number of columns.
Thanks

 

[JGordon11]

Is this an example of the transformation you're describing?
Source step is an example starting table - replace it with your table.
It groups on a column called Name. If your grouping column is called something else then change Name to the correct column name in 5 places

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
        "i45WclTSUQpOTUnMA9LuvkDCyMDQAsQuSq1UitWBKAjITM4uLQAy3PKLUsBqjAyBlFNOYnI2XFFwaBiQdM5ILatEGBOUmgJW4ARkhyFbYmQApMIzMktS4fJwWxBmgFUhrHFGcmxIanFOIkSRE
        bJ7XeBO8cjPS4GoMASpCM7MKUstgqvB9JOhJcic/JwUNHNgwWKM4mIX7G5BCpdYAA==", BinaryEncoding.Base64), 
        Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, Make = _t, Year = _t, Color = _t]),

        
    tbl = Table.SelectRows(Source, each List.Count(List.PositionOf(Source[Name], [Name],2))>1),
    tbl1 = Table.Group(tbl, {"Name"}, {{"all", each {_[Name]{0}} & List.Combine(Table.ToRows(Table.SelectColumns(_, List.Skip(Table.ColumnNames(_),1))))}}),
    maxCol = List.Accumulate(tbl1[all], 0, (s,c)=> if List.Count(c) > s then List.Count(c) else s),
    tbl2 = Table.TransformColumns(tbl1, {{"all", each _ & List.Repeat({null}, maxCol - List.Count(_))}}),
    ColNames = {"Name"} & List.Accumulate({1..(maxCol-1)/(Table.ColumnCount(Source)-1)}, {}, (s,c)=> s & List.Transform(List.Skip(Table.ColumnNames(Source),1), each _ & "." &  Text.From(c))),
    tbl3 = Table.FromRows(tbl2[all],ColNames)

in
    tbl3