"16.0\powequery\Cache\Caches" filling up disk space

[JaydonT]

Hi all,

Please note this is all through using power query in excel, not powerBI

I'm having a problem where the directory AppData\Local\Microsoft\Office\16.0\powequery\Cache\Caches is filling my disk space and I have to manually browse to the folder and delete its contents.

The clear cache option under Query Options > Global > Data Load > Data Cache Management Options does work on the cache folder, but not the caches subfolder. And I do have the default maximum allowed field filled in at 4096 which i can see is being respected in the cache folder, but not the caches subfolder.

From what I can gather the Cache directory contains all of the cached data for all of the queries in your workbook, while the Caches directory contains the individual cache files for each query. When you run a query in Power Query, it will create a new cache file in the Caches directory, which is used to store the results of the query.

But I dont understand how to stop this, limit this, or turn it off

[JonnieM2]

I am also having this same issue with Excel installed on an Azure VM running Power Query against an Azure SQL database.

[JaydonT]

In this case if you cant do what I did with the fslogix exclusions, then you need to figure out which query is failing and why. I'd recommend calling Microsoft for assistance with all that.

Since the solution on this page is comprehensive about the issue theres no real point adding more updates. I dont believe microsoft will see this thread and make any changes based on it.

[v-jingzhang]

Hi @JaydonT 

I don't find a setting to limit the size of the sub Caches folder. If you want to delete the files in the folder but don't want to manually do that, you can use the Task Scheduler on your computer to delete files periodically. This could be  a workaround. 

Reference:

Creating A Scheduled Task To Automatically Delete Files Older Than X In Windows

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[JaydonT]

Unfortunately thats not an option because i use this in an Azure Virtual Desktop environment and have to get admins to clear this.

@v-jingzhang I don't quite understand why the sub caches folder is filling up anyway. I didn't think I'm doing enough to fill it up, is there anything that would be doing it that isn't intuitive to me as a user?

[v-jingzhang]

Hi @JaydonT 

Sorry I don't find any document explaining how this sub Caches folder works. I'm using an Azure Virtual Desktop environment and a virtual machine environment. But in both environments, there is no file in the sub Caches folder. In my AVD, there is no Caches folder. While in my VM, the Caches folder does exist but it's always empty. I'm confused whether this is due to that I haven't done any operations that would generate cache files in the sub Caches folder. 

[JaydonT · Accepted solution]

Microsoft came back and basically the Caches folder is specifically for temp cache storage when a query is being loaded, and once the query finishes it deletes its corresponding folder and cache, so it should never ever have any items remaining.

However, if the query fails to complete for whatever reason (like excel crashing) then it wont clean up that queries cache content in caches so if you are having problems you'll need to check your queries and stuff.

In my case, theres no good reason why this users one isnt removing the data, but because its an AVD environment with FSLogix all sorts of crap could be coming in to play

and since other users running the same queries arent getting this problem its even harder to diagnose. My solution for this is to basically use the FSLogix redirections.xml to exclude the caches folder from being moved to the VHD so that the user can manually delete stuff if necessary, and then when he logs off the local_username directory will be wiped clean anyway.

[JaydonT]

Thanks for checking.

It has to be some type of query thats being run, but it seems so shortsighted to have 2 cache folders 1 of which that doesn't appear to have a limit and can grow on the disk indefinitely.

I'll raise a ticket with Microsoft directly then to get an explanation for this and if I get something conclusive I'll report back.

[JaydonT]

This person is having similar issue:

Power Query caches folder keeps increasing - Microsoft Power BI Community