Power Query Remove Duplicates with Condition

[DaveyD]

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]

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

 

[DaveyD]

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]

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

1. set Name to type text
2. sort Name asc
3. sort Extension desc
4. add prefix 0 to the Name
5. Remove duplicates from Name
6. Extract text after delimiter 0

After

 

[DaveyD]

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

 

[sandy666]

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]

Maybe use column filter for file extension

 

[sandy666]

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

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

 

[citizenbh]

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