Forum Discussion
Solved
Create Columns from Duplicate Rows
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
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 scdlet 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 sdcHi 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
2 Replies
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
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 scdlet 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