Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 |
Solved! Go to 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.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
77 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
70 | |
63 | |
55 | |
48 | |
46 |