M query using folder.contents questions

[Mrquestionmark]

I have almost 20K files in the folder and I only need to load some of files based on naming. In my M query, I am using folder.contents first and then filter out the files after.

But the challenge is folder.contents always load all 20K files even I only need few files. The performance is unacceptable. I do not own the files and could not change the files and folder. How can I filter out the files in folder.contents? or is there any function I can use?

Thanks,

W

[Anonymous · Accepted solution]

Spread out the files into different folders

[Anonymous]

or you can generate a file index file, load it, filter, load the required files after filtering

[Mrquestionmark]

Hi GSun,

Thanks for your quick turnaround. 

I am not the owner of the files and not the option to spread the files. 

For your second suggestions, how can I make it in M query to only load the files I need?

Thanks,

W

[Anonymous · Accepted solution]

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("D:\folder\index.txt"), null, null, 936)}),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Column1], "wanted")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "path", each "D:\folder\" & [Column1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Content", each File.Contents([path]))
in
    #"Added Custom1"

[Mrquestionmark]

Hi Gsun,

This is smart. It worked.

Thans for your help!

W

[Anonymous · Accepted solution]

Spread out the files into different folders