Forum Discussion
Merging Two Tables - Keep Order of Rows from Secondary Table in the Merged Table
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?
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.
3 Replies
Hi RokuCap ,
What if you add an index column to Table B before the merge, and then order the merged table by index column?
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.Thanks Yalan. This did the trick!
