New column with concatenated values based on 2 matching IDs

[wad11656]

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

[latimeria · Accepted solution]

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

[wad11656]

Thank 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_]

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.