Forum Discussion

LYorkToenniges's avatar
LYorkToenniges
Helper III
5 months ago
Solved

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 ...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    4 months ago

    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 Community

     

    In 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.

grazitti_sapna's avatar
grazitti_sapna
Super User
5 months ago

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!

LYorkToenniges's avatar
LYorkToenniges
Helper III
5 months ago

 

  • 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.