Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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?
Solved! Go to 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.
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!
Hi @RokuCap ,
What if you add an index column to Table B before the merge, and then order the merged table by index column?
Check out the March 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
20 | |
9 | |
9 | |
8 | |
7 |