Forum Discussion
Helper ICustom Column that concat values of a column that has the same ID (appears more than once)
I have three separate tables that each serves a purpose, and I am creating a 'main' table (with just a few key columns) to join all of them. To keep track of which row item came with from, I added a "Table Location" column. There are some IDs that appear in 2 or more of these tables. Is there a Power Query script that can help me resolve in such a way as the tables below (left is original, right is desired results) so I can then delete the duplicates?
ID | Info | Table Loc | ID | Info | Table Loc | |
1 | T | A | 1 | T | A, C | |
2 | V | A | 2 | V | A, B | |
3 | H | A | 3 | H | A, C | |
4 | M | A | 4 | M | A | |
5 | J | A | 5 | J | A, B | |
6 | P | A | 6 | P | A, B, C | |
2 | V | B | 2 | V | A, B | |
5 | J | B | 5 | J | A, B | |
6 | P | B | 6 | P | A, B, C | |
1 | T | C | 1 | T | A, C | |
3 | H | C | 3 | H | A, C | |
6 | P | C | 6 | P | A, B, C |
Yes, you can run Group on first 2 columns. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately.)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoBYkelWJ1oJSMgKwzOMwayPOA8EyDLF84zBbK84DwzICsAwxQnFJVOKCohPJjtzij2OaOoBPJiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Info = _t, #"Table Loc" = _t]), #"Grouped Rows" = Table.Group(Source, {"ID", "Info"}, {{"Table Loc", each Text.Combine([Table Loc],", "), type nullable text}}) in #"Grouped Rows"
1 Reply
- Vijay_A_Verma
Most Valuable Professional
Yes, you can run Group on first 2 columns. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately.)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoBYkelWJ1oJSMgKwzOMwayPOA8EyDLF84zBbK84DwzICsAwxQnFJVOKCohPJjtzij2OaOoBPJiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Info = _t, #"Table Loc" = _t]), #"Grouped Rows" = Table.Group(Source, {"ID", "Info"}, {{"Table Loc", each Text.Combine([Table Loc],", "), type nullable text}}) in #"Grouped Rows"