Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |