Looking for tips on using folder as data source

[G_Whit-UK]

Hi

I'm looking for some tips on how best to efficiently use data stored in a folder as a data source.  At the moment, the model is inputing a number of CSV files (one per day) from a designated folder.  The CSV files are quite large and the Desktop version of Power Query appears to be struggling to cope with the volume of data.  I am therefore wondering how best to perform the intial steps within Power Query to maximise the efficiency.  I am wondering if it is a good idea to perform any bespoke steps within the "Transfer Sample File" - or would this make things worse?

Any useful tips on how the efficiently use data within a designated folder as a source would be approcated.

Thanks.

[edhans · Accepted solution]

If you need all of the files in the folder, not much you can do. But if you only need some of the files, if the file names or other criteria, like the file date.

FOr example, in this M code, I am doing about 8-9 things where I filter for two files that begin with the letters M and P, get the latest of each, then combine (append) them into a single listing in Sharepoint, and then I hit the Combine button, where the red square is. That is where Power Query creates all of its custom steps for the combine operation. So hundreds of files, but only two get processed each refresh.

[Jimmy801 · Accepted solution]

Hello @G_Whit-UK 

what I'm always doing is the following

- first only filter for files your really need

- create a function to read from your CSV-file (maybe already removing columns, filtering, etc.)

- apply this funciton on the files found in the folder as a new column

- combine the tables created in your new column

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[edhans · Accepted solution]

If you need all of the files in the folder, not much you can do. But if you only need some of the files, if the file names or other criteria, like the file date.

FOr example, in this M code, I am doing about 8-9 things where I filter for two files that begin with the letters M and P, get the latest of each, then combine (append) them into a single listing in Sharepoint, and then I hit the Combine button, where the red square is. That is where Power Query creates all of its custom steps for the combine operation. So hundreds of files, but only two get processed each refresh.