Remove duplicates and keep only last revision

[rippernash]

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.

Table: CURRENT

Index	Name	Part Number	Revision
1	1000003-B0	1000003-B0-A	A.1
2	1000003-B0	1000003-B0-B	B.1
3	1000003-B0	1000003-B0-C	C.1
4	1000003-B0	1000003-B0-D	D.1
5	1000004-B0	1000004-B0-A	A.1
6	1000004-B0	1000004-B0-B	B.1
7	1000004-B0	1000004-B0-C	C.1
8	1000004-B0	1000004-B0-D	D.1
9	1000008-B0	1000008-B0-B	B.1
10	1000008-B0	1000008-B0-C	C.1
11	1000008-B0	1000008-B0-D	D.1
12	1000008-B0	1000008-B0-E	E.1
13	1000008-B0	1000008-B0-F	F.1
14	1000008-B0	1000008-B0-G	G.1

TABLE: DESIRED

Index	Name	Part Number	Revision
1	1000003-B0	1000003-B0-D	D.1
2	1000004-B0	1000004-B0-D	D.1
3	1000008-B0	1000008-B0-G	G.1

[davehus · Accepted solution]

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.