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
Highlighted
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

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
Highlighted
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.

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 Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

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: 227 members 2,510 guests
Please welcome our newest community members: