Create Columns from Duplicate Rows

[katemke]

I have a table that lists a Record ID and a Name of a participant as such:

RECORD ID	NAME
1	Jim Smith
2	Jane Johnson
3	Nick Peters
4	Kathy James
5	Jim Smith
6	Joe Doe
7	Nick Peters

I want to transform this table to create a new table that lists individuals with all associated Record IDs, as such

Name	Record ID 1	Record ID 2
Jim Smith	1	5
Jane Johnson	2	null
Nick Peters	3	7
Kathy James	4	null
Joe Doe	6	null

Individuals can potentially have more than 2 record IDs but it is unlikely.  Wondering how I can transform the data in Power Query to create this new table

[Anonymous · Accepted solution]

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

[AlB · Accepted solution]

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 

[Anonymous · Accepted solution]

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