Create Columns from Duplicate Rows

this code should work also in case you have more than two recIDs by name

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKzFUIzs0syVCK1YlWMgKJJOalKnjlZ+QV5+eBBY2Bgn6ZydkKAaklqUXFYDEToJh3YklGpYJXYm4qRMwUwzgzkEh+qoJLfiqYb45uUiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"RECORD ID" = _t, NAME = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"RECORD ID", Int64.Type}, {"NAME", type text}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"NAME"}, {{"recID", each _[RECORD ID]}}),
    #"Valori estratti" = Table.TransformColumns(#"Raggruppate righe", {"recID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    scd = Table.SplitColumn(#"Valori estratti", "recID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv))
in
    scd

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKzFUIzs0syVCK1YlWMgKJJOalKnjlZ+QV5+eBBY2Bgn6ZydkKAaklqUXFYDEToJh3YklGpYJXYm4qRMwUwzgzkEh+qoJLfiqYb47FJAs0NZYYphgbYwgZGqCbFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"RECORD ID" = _t, NAME = _t]),
    #"Raggruppate righe" = Table.Group(Origine, {"NAME"}, {{"recID", each _[RECORD ID]}}),
    #"Valori estratti" = Table.TransformColumns(#"Raggruppate righe", {"recID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    sdc = Table.SplitColumn(#"Valori estratti", "recID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv))
in
    sdc

Hi katemke 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKzFUIzs0syVCK1YlWMgKJJOalKnjlZ+QV5+eBBY2Bgn6ZydkKAaklqUXFYDEToJh3YklGpYJXYm4qRMwUwzgzkEh+qoJLfiqYb45uUiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"RECORD ID" = _t, NAME = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RECORD ID", Int64.Type}, {"NAME", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"NAME"}, {{"Count", each Text.Combine(List.Transform([RECORD ID], each Text.From(_)), "|") }}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Count", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Count.1", "Count.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Count.1", Int64.Type}, {"Count.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Count.1", "Record ID 1"}, {"Count.2", "Record ID 2"}})
in
    #"Renamed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers