Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.