cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AMaxfield
Regular Visitor

Removing duplicates by two columns of data

Hi All

 

I have the folwing data, please see the example looking at the first four rows;

AMaxfield_0-1626177337420.png

I want to be able to remove the duplicate values of "W/Order" value WA00334 lusing "Op No" and "Date".

 

I only want to retain the higest "Op No" e.g. 25 and the latest date e.g. 22/01/20.

 

So a filter that woudl remove linew 1 to 3 and retain 4 and then effectivly the same for all other data in the table.

 

Any help appreciated 

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

Hi @AMaxfield ,

you must sort by W/Order, Op No and date in descending order. Then use Table.Buffer to keep that sort order.

If you then remove dups on W/Order it will keep only the topmost row.

More reading here: Bug warning for Table.Sort and removing duplicates... - Microsoft Power BI Community

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
AMaxfield
Regular Visitor

Hi Imke

 

I was not aware of Table.Buffer and it all wroked as you suggested, many thanks for your help.

 

Andy

ImkeF
Super User II
Super User II

Hi @AMaxfield ,

you must sort by W/Order, Op No and date in descending order. Then use Table.Buffer to keep that sort order.

If you then remove dups on W/Order it will keep only the topmost row.

More reading here: Bug warning for Table.Sort and removing duplicates... - Microsoft Power BI Community

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors