Combining rows with the same ID in one row by adding the columns at the back

[bluebock]

Dear all!

How can I combine rows with the same ID into one row by adding the columns at the back of the first row? Please find an explanation in the picture:

Thank you so much for your help!

[bluebock]

Hi @jbwtp 

That's amazing! It worked perfectly fine! Thank you so much, big help!

We are discussing you suggestion.

Thanks again and all the best!

Kind regards

[jbwtp · Accepted solution]

Hi @bluebock,

This is doable (see below), but I would suggest that you re-think the PQ model. Doing pivoting this way is quite strage, do you really need it?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lGqTC0GkoZGekBkBBIoSC4CkfnFmSWZZalKsTpQhXn5QMLPEYkASxkrIAwxhhuSmA7SkZqeiDDDCKHOBM0yVIVgE8G2GRrqARFYIYqdCHcbmegZGOsZGeEwygSu0MASbhQuD2J4Al1hLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"w test" = _t, #"w test.date" = _t, #"w .test.method" = _t, #"w test.result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"w test", type text}, {"w test.date", type text}, {"w .test.method", type text}, {"w test.result", type text}}),
    
    
    fCombine = (x as table) => 
        let 
            #"Added Index" = Table.AddIndexColumn(x, "Index", 0, 1, Int64.Type),
            Pivot = List.Accumulate(List.Skip(Table.ToRecords(#"Added Index")), #"Added Index"{0}, (a, n)=>  
                                                            let 
                                                                fields = Record.RemoveFields(n, {"ID", "Index"}),
                                                                names = Record.FieldNames(fields),
                                                                renamed = List.Transform(names, each _ & "." & Text.From(n[Index])),
                                                                out = a & Record.RenameFields(fields, List.Zip({names, renamed}))
                                                            in out),
            result = Table.FromRecords({Pivot})
        in result,

    #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"ID"}, {{"Data", fCombine}})[Data])
    
    
in
    #"Grouped Rows"

Kind regards,

John