Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
rippernash
New Member

Remove duplicates and keep only last revision

I have the below data in the first table "Current Data" that has duplicates. The duplicates are in the column "Name" but each value has a unique Part number in the "Part number" column. I want to be able to use power query to delete all the duplicate values in the "Name" column and keep only the instance that has the highest alphabet in the final character of the part number. You can see the desired output table for how I want it to work. 

I tried using the remove duplicates option with a buffer table but sorting doesnt seem to work well with string characters. Any help on this is appreciated.

 

rippernash_0-1663693523488.png

Table: CURRENT

IndexNamePart NumberRevision
11000003-B01000003-B0-AA.1
21000003-B01000003-B0-BB.1
31000003-B01000003-B0-CC.1
41000003-B01000003-B0-DD.1
51000004-B01000004-B0-AA.1
61000004-B01000004-B0-BB.1
71000004-B01000004-B0-CC.1
81000004-B01000004-B0-DD.1
91000008-B01000008-B0-BB.1
101000008-B01000008-B0-CC.1
111000008-B01000008-B0-DD.1
121000008-B01000008-B0-EE.1
131000008-B01000008-B0-FF.1
141000008-B01000008-B0-GG.1


TABLE: DESIRED

IndexNamePart NumberRevision
11000003-B01000003-B0-DD.1
21000004-B01000004-B0-DD.1
31000008-B01000008-B0-GG.1
1 ACCEPTED SOLUTION
davehus
Memorable Member
Memorable Member

Hi @rippernash ,

 

Please see solution below. If you step through it you can see how it's done.

Split Revision Number to get letter

Sorted by Part asc and then then revision letter descending.

Group by and created and index by each part number.

Filtered to 1.

 

You may need to tweak to suit your data but it gives you something to work off.

 

Did I help you today? Please accept my solution and hit the Kudos button.

View solution in original post

1 REPLY 1
davehus
Memorable Member
Memorable Member

Hi @rippernash ,

 

Please see solution below. If you step through it you can see how it's done.

Split Revision Number to get letter

Sorted by Part asc and then then revision letter descending.

Group by and created and index by each part number.

Filtered to 1.

 

You may need to tweak to suit your data but it gives you something to work off.

 

Did I help you today? Please accept my solution and hit the Kudos button.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.