Forum Discussion
Power Query Refresh loading unused file, saying size is multiple times its actual size
I'm not sure what is happening so I'll just give you the rundown.
I am refreshing a table in Power Query, but when I do the bottom corner show it is load an unrealated file not used in the table at all. This unused file is also showing an enormous size (see snip)
This file is actually 45ish MB. Its actually still going, and now reads 299.
What in the blue moon is going on? I have no reference in the table to that file, the file is a fraction that size, and that table that does use this file should only l;oad a single sheet from it to begin with, not the entire file.
Please let me know what else you may need to know. I have tried to make sure it's only refreshing this table, I did not select refresh all.
Hi LYorkToenniges ,
Thanks for reaching out to the Microsoft Fabric Community, and for sharing the additional details.
From what you described, this behavior aligns with how Power Query evaluates queries rather than an unrelated file being loaded.
Power Query may evaluate the same data source multiple times. If a file is referenced anywhere in the model, especially through referenced queries, it can be evaluated repeatedly during refresh. For example, if multiple queries reference the same source, it may be executed multiple times rather than once and shared.
This is documented here:
Why does my query run multiple times - Power Query | Microsoft Learn
Solved: Re: loading the source several times - Microsoft Fabric CommunityIn this setup, even if a base query (such as the one pointing to your Excel file) is not loaded, it can still be re-evaluated when another query depends on it. This can make it appear as if an unrelated file is being loaded.
Also, Excel sources do not support query folding, so transformations are processed within Power Query. This can increase in-memory processing and is one of the reasons you may see the size during refresh appearing larger than the actual file size.
Regarding your expectation that only a single sheet should be read, the Excel.Workbook function first retrieves the workbook structure (all sheets/tables/metadata), and filtering to a specific sheet is applied afterward.
Given this, it would be helpful to confirm whether that file is used anywhere else in the model, even indirectly (for example through another query). If so, the behavior you are seeing would be expected.
If the same behavior occurs with a fully isolated query, feel free to share a simplified version of the query structure and we can take a closer look.
Hope this helps. Please reach out for further assistance.
Thank you.
7 Replies
Hi LYorkToenniges ,
Hi ,
Here are few things you can try
- Disable Background Preview - File -> Options -> Data Load then turn off (a.) Background preview (b.) Parellal Loading
- Remove not required loads, if query/table is not needed either disable load or delete (Right click on query)
- Buffer table in advance (Avoids loading table multiple times) Source = Table.Buffer(Excel.Workbook(File.Contents("path")))
- Remove unwanted columns in the very next step when data is loaded
- Cobined similar steps into one applied step, e.g. renaming columns, changing column types
- Also check query dependency and remove unwanted tables in transform View -> Query Dependencies
Hope these steps will help you reduce the load.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!- Disable Background Preview - File -> Options -> Data Load then turn off (a.) Background preview (b.) Parellal Loading
- I have already done this since I had a much larger data collection for a different Dashboard and couldn't get it to refersh reasonably without doing this. Honerstly it was poorly optimized, I've learned a lot since, but anyway, this is done
- Remove not required loads, if query/table is not needed either disable load or delete (Right click on query)
- This is also done. The file is referenced in another table that is curcial for the dashboard, but that table shouldn't be refreshing when I click refresh preview on the current table as best I understand it.
- Buffer table in advance (Avoids loading table multiple times) Source = Table.Buffer(Excel.Workbook(File.Contents("path")))
- This I had not considered. I don't believe any table is loaded multiple times, but I will check. While this may speed it up, I don't see how this would effect the ghost file refernce that seems to be loading. Copuld you explain it a little bit more to me?
- Remove unwanted columns in the very next step when data is loaded
- How would this effect the ghost file loading? (also, I am only loading certain columns to begin with, so nothing to delete.)
- Cobined similar steps into one applied step, e.g. renaming columns, changing column types
- While I do this for a cleaner interface for others who come after me, I don';t seee what this might have to do with the ghost file.
- Also check query dependency and remove unwanted tables in transform View -> Query Dependencies
Hope these steps will help you reduce the load.
- This I will double check. There might be something there. I will keep you updated.
- Disable Background Preview - File -> Options -> Data Load then turn off (a.) Background preview (b.) Parellal Loading
Hi LYorkToenniges ,
Thanks for reaching out to the Microsoft Fabric Community, and for sharing the additional details.
From what you described, this behavior aligns with how Power Query evaluates queries rather than an unrelated file being loaded.
Power Query may evaluate the same data source multiple times. If a file is referenced anywhere in the model, especially through referenced queries, it can be evaluated repeatedly during refresh. For example, if multiple queries reference the same source, it may be executed multiple times rather than once and shared.
This is documented here:
Why does my query run multiple times - Power Query | Microsoft Learn
Solved: Re: loading the source several times - Microsoft Fabric CommunityIn this setup, even if a base query (such as the one pointing to your Excel file) is not loaded, it can still be re-evaluated when another query depends on it. This can make it appear as if an unrelated file is being loaded.
Also, Excel sources do not support query folding, so transformations are processed within Power Query. This can increase in-memory processing and is one of the reasons you may see the size during refresh appearing larger than the actual file size.
Regarding your expectation that only a single sheet should be read, the Excel.Workbook function first retrieves the workbook structure (all sheets/tables/metadata), and filtering to a specific sheet is applied afterward.
Given this, it would be helpful to confirm whether that file is used anywhere else in the model, even indirectly (for example through another query). If so, the behavior you are seeing would be expected.
If the same behavior occurs with a fully isolated query, feel free to share a simplified version of the query structure and we can take a closer look.
Hope this helps. Please reach out for further assistance.
Thank you.I did in fact disconnect a query reference and independantly load the source file instead of referencing the other query because that was exactly what was happening. Why it was making the file genuinely insanely enormous I still don't know, but Streamlining the dependancies helped a lot. M language makes me feel like a kid playing in their dad's toolbox and if I actually make something it's more luick and chance than skill. I have GOT to do some deep dives into tutorial etc.
got above 450 before I finally quit. I have cleared cache as well, doesn't seem to help.
Hi LYorkToenniges , Can you provide your Power Query for that table to better understand the issue?
ThanksSince it would be posted here I don't thing I can legally do that. Even if I obscure identifying information, the code is technically UWM property, and other could copy it (although why, I don't know, it's a mess). Let me see how much I can "Obscure" it and check with my Supervisor. That may take until later today or tomorrow though.
