Transpose multiple rows in to multiple columns

Hi Anonymous ,

How about this:

let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoOcgaSIe4hCkDK0MgYSFbHKBUXJceHVBbEKFnFKDnFKOnEKJWklyCL1ALVBSQWFyvF6hAyJzk/JwmszQVuEFwIapJbYmYOcSYlozkJWQjhplgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"SRC TABLE" = _t, #"TGT TABLE" = _t, KEY = _t, #"column value" = _t, Status = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"SRC TABLE", type text}, {"TGT TABLE", type text}, {"KEY", Int64.Type}, {"column value", type text}, {"Status", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "SRC TABLE", "TGT TABLE", "KEY"}, {{"Grouping", each SubGroup(_), type table [column value=nullable text, Status=nullable text]}}),
        SubGroup = (Table as table) as table =>
        let
            #"Removed Columns 1" = Table.RemoveColumns(#"Table",{"ID", "SRC TABLE", "TGT TABLE", "KEY"}),
            #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns 1", {}, "Attribute", "column"),
            #"Removed Columns 2" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
            #"Transposed Table" = Table.Transpose(#"Removed Columns 2")
        in
            #"Transposed Table",
        ColNames = Table.ColumnNames ( Table.Combine ( #"Grouped Rows"[Grouping] ) ),
        Custom = Table.ExpandTableColumn ( #"Grouped Rows", "Grouping", ColNames )
    in
        Custom

Let me know 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/