Forum Discussion
Advocate INew column with concatenated values based on 2 matching IDs
In Power Query, How can I create a new column that combines the values from 1 column, if the values in 2 columns match?
In this case, how can I concatenate Contact_Type values when the Contact_ID and Order_ID match?
(I prefer not to use Grouping and having to worry about managing all the rest of my columns, ex. the Dummy_Data column)
| Order_ID | Contact_ID | Contact_Type | Contact_Name | Dummy_Data (Don't mess with me) |
| 1 | 4 | SHIPPING | Megan | 1234 |
| 1 | 4 | BILLING | Megan | 1234 |
| 2 | 12 | SHIPPING | Pete | 1234 |
| 2 | 17 | BILLING | Rob | 1234 |
| 3 | 4 | SHIPPING | Megan | 1234 |
| 3 | 12 | BILLING | Pete | 1234 |
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
| Order_ID | Contact_ID | Contact_Type | Contact_Name | Dummy_Data (Don't mess with me) | Contact_Type-mod |
| 1 | 4 | SHIPPING | Megan | 1234 | SHIPPING & BILLING |
| 1 | 4 | BILLING | Megan | 1234 | SHIPPING & BILLING |
| 2 | 12 | SHIPPING | Pete | 1234 | SHIPPING |
| 2 | 17 | BILLING | Rob | 1234 | BILLING |
| 3 | 4 | SHIPPING | Megan | 1234 | SHIPPING |
| 3 | 12 | BILLING | Pete | 1234 | BILLING |
Hi wad11656 ,
Try this, using grouping but and you don't have to worry for the remaning columns (you cen even add columns without changing the code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS2K93RR0lFyzs8rSUwuQeWEVBakInH9EnNBXJfS3NzKeJfEkkSlWJ1oJUOgkAkQB3t4BgR4+rkDmb6p6Yl5QNrQyNgERY2Tp48PDiVGYA6qOQGpJakYasxRzAnKT0JWYUyEY4xhNiFMQbEoFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Order_ID", "Contact_ID"}, {{"Rows", each _, type table [Order_ID=nullable text, Contact_ID=nullable text, Contact_Type=nullable text, Contact_Name=nullable text, Dummy_Data=nullable text]}}), Custom1 = Table.TransformColumns(#"Grouped Rows",{{"Rows", (Group)=> Table.AddColumn(Group, "Contact_Type-mod", each Text.Combine(Group[Contact_Type], " & "))}}), Custom2 = Table.Combine(Custom1[Rows]) in Custom2
3 Replies
latimeriaSolution Specialist
Hi wad11656 ,
Try this, using grouping but and you don't have to worry for the remaning columns (you cen even add columns without changing the code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS2K93RR0lFyzs8rSUwuQeWEVBakInH9EnNBXJfS3NzKeJfEkkSlWJ1oJUOgkAkQB3t4BgR4+rkDmb6p6Yl5QNrQyNgERY2Tp48PDiVGYA6qOQGpJakYasxRzAnKT0JWYUyEY4xhNiFMQbEoFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Order_ID", "Contact_ID"}, {{"Rows", each _, type table [Order_ID=nullable text, Contact_ID=nullable text, Contact_Type=nullable text, Contact_Name=nullable text, Dummy_Data=nullable text]}}), Custom1 = Table.TransformColumns(#"Grouped Rows",{{"Rows", (Group)=> Table.AddColumn(Group, "Contact_Type-mod", each Text.Combine(Group[Contact_Type], " & "))}}), Custom2 = Table.Combine(Custom1[Rows]) in Custom2
wad11656Thank you - that seemed to have worked great.
Unfortunately another reason I don't like Grouping is because each time I implement a new Grouping, it seemingly adds 30 minutes to 1hr of report loading time. So now I am stuck waiting forever for my data. Microsoft should optomize their grouping algorithm.
_AlexandreRM_Helper II
Hello wad11656 , you should use groupping and joining.
1st step : group your table on Contact_ID and Order_ID (the thing you didn't wanted to do),
2nd step : join the the groupping step with the step before groupping (to achieve that, you will need to edit manually the code). Then, expand it to retrieve the group step result in the Contact_Type-mod.