Log In
Register
Home
March 3, 2021
Offline
Dear,
I now that it's very easy to remove duplicates via power query, but I have an example where I don't want to remove all duplicates.
For example:
I have material where I have components with 2 cylinder ends which isn't correct. But I also have several disc's, which are correct.
But of the cylinder end, I need only the second, version 3 / M05803300 and I wanted to remove the version 4.
The example is showing only one material number, but I have more then 1.000 is my real example. And both components are existing otherwise it was easy. Can I remove duplicates, without touching all duplicates? See attachement "duplicates" for example
PlantMaterialMaterial DescriptionAlternative BOMExplosion levelComponentComponent DescriptionComponent Qty
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 4 | M05803000 | CYLINDER END | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 3 | M05803300 | CYLINDER END | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 2 | M10010192 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 3 | M100250A1 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 3 | M100250A3 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 3 | M100250A3 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 2 | M100252A7 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 2 | M100252F3 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 2 | M100252K2 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 2 | M100252K4 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 2 | M100252K5 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 2 | M10040108 | DISC | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 2 | M10050301 | DISC | 1 |
See Answer
July 16, 2010
Offline
Hi Johan,
If you don't want the 4 Cylinder End then I'd create another column that concatenates the Explosion Level and Component columns together, then you can use the Filters on that new column to filter out the 4 Cylinder End records.
Mynda
March 3, 2021
Offline
Hey Mynda,
Thanks for your reply.
In those examples, it was indeed explosion level 4, but it can be 3, 5 or 6 as well.
But is always be the highest level. I guess this is to complicated?
I added a few examples.
Kind regards,
Johan
PlantMaterialMaterial DescriptionAlternative BOMExplosion levelComponentComponent DescriptionComponent Qty
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 4 | M05803000 | CYLINDER END | 1 |
| STM1 | M01167210 | SHOCK ABSORBER | 1 | 3 | M05803300 | CYLINDER END | 1 |
| STM1 | M01167218 | SHOCK ABSORBER | 1 | 4 | M05803000 | CYLINDER END | 1 |
| STM1 | M01167218 | SHOCK ABSORBER | 1 | 3 | M05803300 | CYLINDER END | 1 |
| STM1 | ME1021202 | SHOCK ABSORBER | 1 | 4 | M05803300 | CYLINDER END | 1 |
| STM1 | ME1021202 | SHOCK ABSORBER | 1 | 5 | M05803000 | CYLINDER END | 1 |
| STM1 | MEP121402 | OE PACKED | 1 | 5 | M05803300 | CYLINDER END | 1 |
| STM1 | MEP121402 | OE PACKED | 1 | 6 | M05803000 | CYLINDER END | 1 |
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Johan,
If you want to keep some records based on component description, we can build a workaround.
For example, you can add an Index column and another column with this formula:
if Text.Contains([Component Description],"CYLINDER END") then [Component Description] else [Component Description] & Text.From([Index])
This new column will contain values like this:
CYLINDER END
CYLINDER END
DISC3
DISC4
DISC5
This way, Cylinder will be identified as duplicates, but DISC will have an additional unique index added in order to avoid removing them.
You can use this column, with other columns that together will create a unique key to remove duplicates.
It can be any other column, not necessarily the description:
if Text.Contains([Component Description],"CYLINDER END") then [Material] else [Material] & Text.From([Index])
March 3, 2021
Offline
Hey Catalin,
Wow, this is working perfectly. I had to add the material as well, but this is working perfectly.
"if Text.Contains ([Component Description],"CYLINDER END") then [Material]&[Component Description] else [Material]&[Component Description]&[Index]"
Thanks a lot.
Answers Post
1 Guest(s)
