How to filter wrong named files?

[haykp]

Dear Forum,

Please help with my frist steps.

I want to filter out all files, which does not match to some exact pattern.

I have that patter, which is file should end with  _CW<number>_<year>_<month>_<day>_<time>.csv

all other .csv file which does not end with this format I want to filter out.

I have tried multiple options, but cannot solve this issue. These are my files

sales1_res_CW1_2023_05_23_23h23h53s.csv
sales2_res_CW2_2023_04_23_23h23h53s.csv
sales3_res_CW3_2023_03_23_23h23h53s.csv
sales2_res_CW8.csv
sales3_res_CW3_03_23_23h23h53s.csv
sales3_res_CW3_2023_23h23h53s.csv
sales3_res_CW3_23h23h53s.csv

I want to filter our during Transform all the files in red color.

Can someone please help me to write M-Query equation for this please.

( I have tried End with, but it want exact value)

Thanks

Hayk

[PhilipTreacy · Accepted solution]

Hi @haykp 

Download example PBIX file

If you split the column by delimiter at every underscore _ then you can remove any blank rows from the final column (see image below) and you're left with the filenames you want.

Then tidy up by deleting the columns created by the 'split column by delimiter' step.

Duplicate the column first if you want to preserve the filenames.

Regards

Phil