Removing duplicates by specified sort order in Power Query

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to this blog include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Removing duplicates by specified sort order in Power Query
Removing duplicates by specified sort order in Power Query

Posted by Sam Lowrie on 04 September 2026

In Power BI, I have data that includes people and their completion percentage for a form.

I want to return each person once with their highest completion percentage.

Currently, the data is in its original order.

Click the dropdown next to the column you want to sort by (in this case Completion %) and select Sort Descending.

Applying a sort to the completion percentage

Sorting a numeric column in descending order will place the largest values first.

The data is now ordered from the highest completion percentage to the lowest.

Sorting the items by their completion percent highest to lowest

I don't want to remove duplicates from this column, as two people could have the same score.

To keep only one copy of each person, right-click the Person column and choose Remove Duplicates from the menu.

M is case-sensitive, meaning that Sally and sally would be treated as separate values.

This returns one row for each person, but the results are incorrect.

Power BI has ignored the sort order we applied and has instead returned the first item based on the original order of the data.

Remove the duplicates from the person column

For example, Bob 50 appeared before Bob 43, and Sally 20 appeared before Sally 100.

To fix this, we need to apply an action to the Sorted Rows step before the Removed Duplicates step.

Click on the previous step to swap back to the sorted table

Click on the Query Settings | Applied Steps | Sorted Rows step.

Edit the M code at the top of the table, add Table.Buffer( in front of the existing code and a closed bracket at the end.

Adding buffer to save the table in memory to prevent future changes

The Table.Buffer function tells Power BI to freeze the table in its current state, preventing implicit reordering.

Click back to the last final step, Query Settings | Applied Steps | Removed Duplicates to view the end result.

Click back onto the removed duplicates step

Some actions do not create separate steps.

Now, the Remove Duplicates action keeps the first item encountered based on the Completion % sort order.

The most complete row returned for each person

This technique also works when sorting by multiple columns.

Some other pages relevant to this blog include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This blog has 0 threads

Add a new post

ALL BLOGS

POWER BI BLOGS

POWER BI BLOGS

Head office

London

Manchester