Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.