Forum Discussion
Help with transposing rows if column is the same.
I would like to use Power Query to transform the table below (Before) to the (After).
The idea would be to transpose rows (Sub Invoice#, Task Descriton, Price) to additional columns if the (InvoiceNumber) column matches.
Before
Before
After
Thank you!
=let col=Table.ColumnNames(Before) in Table.Combine(Table.Group(Before,"InvoiceNumber",{"n",each Table.FromRows({{[InvoiceNumber]{0}}&List.Combine(Table.ToRows(Table.SelectColumns(_,List.Skip(col))))},List.TransformMany({0..Table.RowCount(_)-1},each if _=0 then col else List.Skip(col),(x,y)=>if x=0 then y else y&" - "& Text.From(x+1)))})[n])
1 Reply
=let col=Table.ColumnNames(Before) in Table.Combine(Table.Group(Before,"InvoiceNumber",{"n",each Table.FromRows({{[InvoiceNumber]{0}}&List.Combine(Table.ToRows(Table.SelectColumns(_,List.Skip(col))))},List.TransformMany({0..Table.RowCount(_)-1},each if _=0 then col else List.Skip(col),(x,y)=>if x=0 then y else y&" - "& Text.From(x+1)))})[n])