Need help to speed up folder source

[radoslavov91]

I have a Folder that received a report from a machine in our factory (working time, working rounds, etc.).

Every new day 00:01AM the machine creates a new report file in a new folder, and every folder is named with the date of the report (all this is automatically done).

In every folder thereis one one file 

Each file is between 400KB and 4MB which is between 400 and 4000 rows 

In Power Bi i'm trying to load all files from the Laser folder, which folder contains multiple folders for each day of the year.

Then i'm filtering only the .csv files (because there also some system generated files .ini which i want to make sure are not loaded or cause error.

Then i filter only folders for year 2024 (and respectively only the files for 2024

These are my full query settings where i rename some columns, remove unneccessary columns, 

Then i expand the files to get the machine results (working time)

Then i do some further correction such as renaming some columns to make more sense, removing uneccessarey columns 

Mostly this is it, i don't create any calculation columns or measurements.

I don't need to tell you how long it take for the file to loads, so my question is, if you guys have any suggestions how to optimaze this file to load the data faster, combine quesries, or any other tips and tricks that you can suggest to overall make this more efficiant. 

It is important to mention that i cannot change the way files are generated by the machine, it is as it is, they are exported as CSV files every mornin. If there is another way combining this files as one post action, im up for trying it.

[johnbasha33 · Accepted solution]

@radoslavov91 

Use Folder Path Parameter: Set up a parameter to dynamically select the folder path for the year 2024, reducing unnecessary data loading.

Combine Queries: Merge multiple query steps to minimize operations during data loading, like filtering .csv files and folders for 2024 together.

Reduce Data Cleaning: Only apply essential data cleaning steps to minimize processing time. Focus on transformations necessary for your analysis.

Query Folding: Utilize query folding to push transformation operations to the data source, leveraging its processing capabilities for faster data retrieval.

Incremental Loading: Implement incremental loading to only load new or updated data since the last refresh, reducing the amount of data loaded each time.

Data Compression: Optimize data compression settings to balance file size and performance, experimenting with different options.

Partitioning: If applicable, partition your data based on criteria like date to improve query performance by accessing relevant partitions only.

Data Model Simplification: Review your data model to remove unnecessary relationships or columns, simplifying it for better query performance and reduced memory usage.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!