Multiple value Column to distinct Column Headers with Values from neighborhood Column

Hi Anonymous ,

Is the number of values corresponding to each id the same? If it is the same, you can try the following M code, the "Source" is in my query and starting with "Changed Type", is in yours:

let    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWBsIzQWEnorFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t]),    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"Count", each _[id], type list}}),
    InsertAndZip = List.Zip(Table.AddColumn(#"Grouped Rows", "Custom", each List.InsertRange(_[Count],0,{[name]}))[Custom]),
    #"Converted to Table" = Table.FromList(InsertAndZip, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),   
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", Int64.Type}, {"b", Int64.Type}})
in
    #"Changed Type2"

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESRlj47hu5pEgJjjnX8GZ1oBCzGq_iesHbzKXW_TbvnyOA?e=6gz4IZ

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.