Merging Two Tables - Keep Order of Rows from Secondary Table in the Merged Table

[RokuCap]

I have two tables: Table A & Table B. I want to merge the Table B values to Table A but keep the row order from Table B (merging messes the row ordering). I've created a Group Rank column (based on the Category value) as a helper column for the merge.

Any help would be greatly appreciated!

Table A

Index	Category	Description	Price ($/kg)
1	Cat A	Apples	5
2	Cat B	Oranges	2
3	Cat C	Pears	3
4	Cat D	Watermelon	4

Table B

Group Rank	Category	Order ID
1	Cat A	77381
2	Cat A	71329
3	Cat A	61086
1	Cat C	52255
2	Cat C	68071

Desired Merge Result:

Category	Description	Price ($/kg)	Group Rank	Order ID
Cat A	Apples	5	1	77381
Cat A	Apples	5	2	71329
Cat A	Apples	5	3	61086
Cat B	Oranges	2
Cat C	Pears	3	1	52255
Cat C	Pears	3	2	68071
Cat D	Watermelon	4

Extra question: I'd also like the price value to only show on the 1st instance of the Category (Group Rank: 1) and be null or zero for rows after with the same value. Is this achievable in PQ?

[v-yalanwu-msft · Accepted solution]

Hi, @RokuCap ;

After we merge two table, we could sort the column.

= Table.Sort(#"Expanded TableB",{{"Index", Order.Ascending}, {"TableB.Group Rank", Order.Ascending}})

Then the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RokuCap]

Thanks Yalan. This did the trick!

[Luis98]

Hi @RokuCap ,

What if you add an index column to Table B before the merge, and then order the merged table by index column?