cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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?

1 ACCEPTED SOLUTION
Community Support

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 3
Community Support

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.

Helper I

Thanks Yalan. This did the trick!

Resolver II

Hi @RokuCap ,

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

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors