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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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