cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kleetus51 Member
Member

Remove duplicates based on sort in Power Query

I have a table that has ID and date columns. I want to remove duplicates and only keep the most recent record for each ID. I thought I'd by able to simply by sorting either ascending or descending then removing duplicates, but it doesn't keep the first or last record in your sort. It only keeps the first record in the source dataset regardless of sort. Is there a way around this?

2 ACCEPTED SOLUTIONS

Accepted Solutions
AlexisOlson Member
Member

Re: Remove duplicates based on sort in Power Query

I've run into this before. I think it's a result of query folding not operating in the order that you'd expect.

 

The workaround that worked for me was to add an index column after sorting before removing duplicates. This breaks query folding and forces it to operate in the order you just defined by your sort.

View solution in original post

Highlighted
AnkitBI Established Member
Established Member

Re: Remove duplicates based on sort in Power Query

@AlexisOlson  @kleetus51

 

Do check this video from curbal.com. It beautifully explains the reason why changing Sort Order in Power query doesn't work for removing duplicates and using Table.Buffer to keep new Sorting Order in memory.

 

Curbal.com

 

Sharing this as it might be helpful in some other scenarios too..

View solution in original post

3 REPLIES 3
AlexisOlson Member
Member

Re: Remove duplicates based on sort in Power Query

I've run into this before. I think it's a result of query folding not operating in the order that you'd expect.

 

The workaround that worked for me was to add an index column after sorting before removing duplicates. This breaks query folding and forces it to operate in the order you just defined by your sort.

View solution in original post

kleetus51 Member
Member

Re: Remove duplicates based on sort in Power Query

Thanks Alexis! Worked perfectly.

Highlighted
AnkitBI Established Member
Established Member

Re: Remove duplicates based on sort in Power Query

@AlexisOlson  @kleetus51

 

Do check this video from curbal.com. It beautifully explains the reason why changing Sort Order in Power query doesn't work for removing duplicates and using Table.Buffer to keep new Sorting Order in memory.

 

Curbal.com

 

Sharing this as it might be helpful in some other scenarios too..

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 301 members 2,676 guests
Please welcome our newest community members: