loading the source several times

[ahmedshalabyy12]

Hello All,

I get the data from an Excel sheet and make some transformations; the source is only 10mb in the first 3 steps. The sheet was loading only 10mb in the Power Query, but after several step,s the sheet was loading 40mb  i took a screenshot while it was loading from 26 to 40mb 

what's the reason for that 

Thank you 

[selimovd · Accepted solution]

Hey @ahmedshalabyy12 ,

Chris Webb had a great article/video a few years ago why data sources are being loaded multiple times:

Chris Webb's BI Blog: Why Does Power BI Query My Data Source More Than Once?

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Connect with me: LinkedIn

[Royel · Accepted solution]

Hi, @ahmedshalabyy12 

Excel files don't support query folding, which means transformations cannot be pushed back to the data source. So, each transformation step requires Power Query to reload the entire dataset from Excel, process it in memory, then apply the next transformation. That's why you are experiencing more memory usage than the actual size. 

Some helpful articles from Microsoft Learn: 

1.  Query evaluation and query folding in Power Query

2. Why does my query run multiple times?

If it helps ✔ Give a Kudo • Mark as Solution – help others too!

[v-sshirivolu · Accepted solution]

Hi @ahmedshalabyy12 ,
Thank you for reaching out to Microsoft Fabric Community Forum.

Here are some steps to help address the file size increase:

Optimize Column Expansion: Use Table.Buffer at the start of your query to avoid processing the same data multiple times. Then, use "Choose Columns" to keep only the necessary fields before expanding.
https://learn.microsoft.com/en-us/power-query/optimize-expanding-table-columns

Pre-Process the Excel File: Clean up the source file by deleting unused rows, columns, and extra formatting. Reload it into Power Query to reduce its size and make transformations more efficient.
https://learn.microsoft.com/en-us/power-platform/well-architected/performance-efficiency/optimize-da...

Avoid Data Model and Split Queries: Load the data as "Connection Only" to prevent issues with compression. If your query has many steps, consider splitting it into smaller queries to better manage memory and minimize size increases.
https://learn.microsoft.com/en-us/powerquery-m/table-distinct
Try these steps in Power Query Editor to help identify the cause and improve performance.

Thank you.

[Royel · Accepted solution]

Hi, @ahmedshalabyy12 

Excel files don't support query folding, which means transformations cannot be pushed back to the data source. So, each transformation step requires Power Query to reload the entire dataset from Excel, process it in memory, then apply the next transformation. That's why you are experiencing more memory usage than the actual size. 

Some helpful articles from Microsoft Learn: 

1.  Query evaluation and query folding in Power Query

2. Why does my query run multiple times?

If it helps ✔ Give a Kudo • Mark as Solution – help others too!

[selimovd · Accepted solution]

Hey @ahmedshalabyy12 ,

Chris Webb had a great article/video a few years ago why data sources are being loaded multiple times:

Chris Webb's BI Blog: Why Does Power BI Query My Data Source More Than Once?

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Connect with me: LinkedIn