Forum Discussion
Quicker refresh with 7 local folder sources
Dear power BI'ers,
I make a weekly report, but the refresh in power query of my local files takes a long time. I use as data source the Folder option, where I put new files in the folder every week. This works way better than how I used to do it in Excel, but refreshing is what takes me the longest time now. I refresh every source manually by clicking on it. Power BI tries to automatically refresh everything when your in power query, but then it misses several sources. Does someone know a faster way to refresh my power query (local folder) sources? Scheduled refresh in the report server is not possible.
Thanks in advance
If you're experiencing slow refresh times in Power Query with local files, here are some tips to optimize the process:
Filter Files Early: In Power Query, apply filters as early as possible to limit the number of files being loaded. For example, you can filter by file extension or date modified to only include relevant files.Combine Files Efficiently: Instead of loading all files individually, consider using the “Combine” feature in Power Query. This allows you to consolidate the data from multiple files into a single table, which can significantly speed up the refresh process.
Reduce Data Volume: If your files contain a lot of unnecessary columns or rows, trim them down before loading them into Power Query. The less data Power Query has to process, the faster it will refresh.
Use Incremental Refresh: Although you mentioned scheduled refresh is not an option, if you can implement incremental refresh, it allows Power BI to only refresh the data that has changed since the last refresh.
Hi UserTheBeatYes ,
To use Incremental refresh, you need to meet the following conditions:
1. You need a Power BI Pro license or above.
2. Your data source needs to be a relational data source, such as SQL Database and Azure Synapse, etc.
3. Your data needs to have a column with data type of date/time in order to support some mechanism to filter data by date.
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#requirements
If you meet the above conditions, you can follow the steps in the following document to set up:
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure
If you do not meet the requirements of incremental refresh, please follow Omid_Motamedise's other suggestions and try them.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
If you're experiencing slow refresh times in Power Query with local files, here are some tips to optimize the process:
Filter Files Early: In Power Query, apply filters as early as possible to limit the number of files being loaded. For example, you can filter by file extension or date modified to only include relevant files.Combine Files Efficiently: Instead of loading all files individually, consider using the “Combine” feature in Power Query. This allows you to consolidate the data from multiple files into a single table, which can significantly speed up the refresh process.
Reduce Data Volume: If your files contain a lot of unnecessary columns or rows, trim them down before loading them into Power Query. The less data Power Query has to process, the faster it will refresh.
Use Incremental Refresh: Although you mentioned scheduled refresh is not an option, if you can implement incremental refresh, it allows Power BI to only refresh the data that has changed since the last refresh.
Thank you for your answer!
Incremental refresh I never heard of. But I have e.g. in one folder 8 files that will be mostly the same as the previous file, but with newer rows added to them. Will incremental refresh work for this?
Hi UserTheBeatYes ,
To use Incremental refresh, you need to meet the following conditions:
1. You need a Power BI Pro license or above.
2. Your data source needs to be a relational data source, such as SQL Database and Azure Synapse, etc.
3. Your data needs to have a column with data type of date/time in order to support some mechanism to filter data by date.
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#requirements
If you meet the above conditions, you can follow the steps in the following document to set up:
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure
If you do not meet the requirements of incremental refresh, please follow Omid_Motamedise's other suggestions and try them.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
