Turn 2 columns into multiple columns based on a non distinct column

Hi  Awollet33 ,

Using below M codes to realize it：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc27DcAgEAPQXa6muA+QYdAV+e+/QSQ3uCHdk2XZY8guRUxNsgw5YIdPOOALrvANN/iBO/zCG/y/WanTFp1Ov0a/Tr9Bv6tNo02nTkzbzENVMj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", type text}, {"Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"Group1", each _, type table [Account Number=nullable text, Code=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Group1],"Code")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Group1"})
in
    #"Removed Columns"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!