# Power Query Remove Duplicates with Condition



## DaveyD (Feb 23, 2020)

I have a query to load files from a folder creating a list of files to work with.
Some of these files end with `.prg` and some with `.lib`.
Many of them appear in the list with both extensions.
I would like to remove the `.lib` version of those instances.
When I run "Remove Duplicates", Power Query removes the `.lib` version (usually).
I thought maybe it has to do with the list order because PQ removes the first in the list.
So, I tried sorting it by file name ascending and then by extension descending so `.prg` will appear first causing PQ to remove the second entry, but this didn't do the trick.
Even more confusing, PQ is not consistent regarding which one it removes. Sometimes it removes the `.lib` version and sometimes it removes the `.prg` version.

How can I get it to always remove the `.lib` version if there is a `.prg` version?


I would provide sample data but the problem is that with sample data it seems to work properly. Maybe it is only with the file system?
Here it is anyway:

Name​Extension​1​.prg​1b​.prg​2​.prg​a1​.lib​a1​.prg​a1addon​.prg​a1addon​.lib​aac​.lib​abc​.prg​acc​.prg​acc​.lib​


----------



## sandy666 (Feb 23, 2020)

maybe try sort asc Name then sort asc Extension
_lib should be above prg for the same Name_
and Remove Duplicates from Name


----------



## DaveyD (Feb 23, 2020)

sandy666 said:


> maybe try sort asc Name then sort asc Extension
> and Remove Duplicates from Name


@sandy666 - thanks for the reply
I can try but I don't understand how that would help
But maybe I wasn't clear enough. I sorted Ascending by name and then descending by extension so that the names will be in order and the .prg version  will appear first. Then I removed duplicates from the Name column.

Please let me know if we are understanding each other.
Thanks


----------



## sandy666 (Feb 24, 2020)

ok, I did mistake with asc vs desc
so
try this way

set Name to type text
sort Name asc
sort Extension desc
add prefix 0 to the Name
Remove duplicates from Name
Extract text after delimiter 0




After


----------



## DaveyD (Feb 24, 2020)

@sandy666 - That seems to work!
But WHY? 
What is the logic behind that?


----------



## sandy666 (Feb 24, 2020)

first you need an idea, if you have it then you only need a few clicks ? ?
test with text prefix

What is PowerQuery
Getting started with PQ
PowerQuery Help
PowerQuery M reference
PowerQuery add-in for XL 2010/2013, all above has PQ built-in
General: Excel Help


----------



## citizenbh (Feb 24, 2020)

Maybe use column filter for file extension


----------



## sandy666 (Feb 24, 2020)

@citizenbh
.lib should stay if there is no .prg for the same Name


DaveyD said:


> always remove the `.lib` version if there is a `.prg` version


----------



## citizenbh (Feb 24, 2020)

Ok!, The second way:

1.Import table1 in PQ
2. Duplicate query and rename in Final
3. In first query "Table1" Remove Duplicate from Name
4. After filter by .lib extension
5. In "Final" query filter column Extension by ".prg"
6. Append query "Table1" to "Final"
7. Remove Duplicate from Name


----------

