Concatenate two rows based on different value in columns

[Junaid11]

Hello,

I want to combine two tables. Both table looks same except one or two columns are different in both of them. I want after combining if any of the row has same Person ID, Vendor ID, Name , Start Date and End Date then other rows of the similar rows should concate nate like below out come as mentioned:

My first table look like this:

Person ID	Name	Vendor ID	Description	Start Date	End Date	Next Step	Work
123	James	A12	A-Z	18/01/2021	18/01/2021	Go for next step	Done

My second table which is same except one other column different from above one is:

Person ID	Name	Info	Vendor ID	Start Date	End Date	Next Step	Work
123	James	Mag	A12	18/01/2021	18/01/2021	Don't Go	Not Done

I want my outcome to be:

Person ID	Name	Vendor ID	Start Date	End Date	Next Step	Work	Description	Info
123	James	A12	18/01/2021	18/01/2021	Go for next step,Don't Go	Done,Not Done	A-Z	Mag

As you can see this new table has both columns which were different in both the tables as well as values which were different in the similar column with same Name , Person ID, Vendor ID , Start Date and End Date have been merged in to one in Next Step and Work column. Kindly help in getting this result.

Thanks

[HotChilli · Accepted solution]

--> "After that, you can Merge Columns as required e.g. the Work column from each table."

You select both columns, right-click and choose Merge Columns

Follow the steps of my solution.

I will help if you are stuck.

[HotChilli]

Merge the Queries in Power Query. (on Person ID).

You can then Expand fields from the 2nd table to choose what you want to return.

After that, you can Merge Columns as required e.g. the Work column from each table

[Junaid11]

Hello @HotChilli ,

The main problem is concatenation. How to achieve that?