Power Query Cache Folders

[nandic]

Location of my Power Query cache is this: C:\Users\username\AppData\Local\Microsoft\Power Query\Cache
When i clear cache and refresh data in Excel there is new .dat file in "Cache" folder which is ok.
But there is another example where client refreshes data every 10 minutes, Excel files are small (a few mb), but cache folders (not a single .bat file) are many times more than that.
Questions:
1) why do i miss "Caches" folder inside folder "Cache"? I only have folders "Index" and "Temp" 
2) why refreshing small Excel files (which import data in Excel Table) can generate huge Cache?
3) Limiting Global options inside Power Query didn't help. What is best practice for managing Power Query Cache and is there any way to automatically clean cache older than for example last 5 days?

Thanks in advance.

Best regards,
Nemanja Andic

[v-henryk-mstf]

Hi @nandic ,

The cache files are used to frequently queried items, in case you loose a step in Power Query, please redefine the Transformation you think is missing from the steps. Also you can restore the files back to cache folder, open Power BI file & perform [Save & Apply] + refresh your PBI Desktop file to see the new transformation are there. After that you can clear up the cache folder.

You can change the maximum cache data in the following settings.

However, I think there is no need to clean anything, every time you run a query it retreaves the latest data. The cache is used only by the UI.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nandic]

Hi @v-henryk-mstf ,
Thank you for the help.
In this case, there is an automation process which refreshes Excel files (which use PowerQuery to connect to data sources). These Excel files are not directly used, but they are used to update several other Excel files or to send them via email.
But each day cache folders are generated and they are very big (~400 mb on a daily basis).
Current option is to create additional automation script to delete these cache folders on a daily basis because they are too big.
My question is if it could be solved only via Excel/PowerQuery? (max cache size limit didn't work)

Best regards,

Nemanja Andic