Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TheBoojum
Frequent Visitor

Enormous load activity for small Excel file

The report set I'm working with takes as its source a table in an Excel spreadsheet with a few thousand rows of fewer than ten fields. The spreadsheet itself is less than 400KB. The .pbix file is less than 800KB.

 

However when I build and run some queries, the activity meter at the bottom right hand corner of the window shows that it is reading / processing over 400MB of data and is very slow to run.

 

How could this be? Have I written some horribly inefficient queries that generate exponential amounts of temporary data?

6 REPLIES 6
v-deddai1-msft
Community Support
Community Support

Hi @TheBoojum ,

 

It seems that you have some steps in query editor makes the data become lager like unpivot columns. Would you please show us the pbix file or m query in advanced editor ? And you can also refer to https://www.linkedin.com/pulse/ten-techniques-optimising-memory-usage-microsoft-power-dejan-pajk to reduce memory usage in power bi .

 

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

 

Best Regards,

Dedmon Dai

TheBoojum
Frequent Visitor

I've had a look at the diagnostics and found two things:

  • Four of the 180-odd log entries took up over 80% of the elapsed time;
  • Another four took up another 10% or so.
  • There was no correlation between elapsed time and resource usage (correlation 0.20 - 0.25. I did like the idea of using PBI to analyse PBI performance data).

TheBoojum_0-1612780228864.png

There were plenty of others either / both with the same operation or similar stats, so I'm none the wiser at this point.

 

What I think I'll do next is replace the spreadsheet with a .csv file to see if that makes any difference. And then maybe decompose some of the queries and look at them bit by bit.

if you can, prepare a PBIX file with dummy data without sensitive information and post it here for us to check on the queries itsel and see if we can help an further optmizing it (post also de data file).





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




v-deddai1-msft
Community Support
Community Support

Hi @TheBoojum , 

 

You can use Query Diagnostics to monitor Power Query Memory Usage. For more details, please refer to https://blog.crossjoin.co.uk/2020/05/21/monitoring-power-query-memory-usage-with-query-diagnostics-i...

 

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

 

Best Regards,

Dedmon Dai

TheBoojum
Frequent Visitor

Thanks Stefano.

I'll give that a go and report back.

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

if both files are as low as you telling tham yes, probably you have some queries problem, now you can use the diagnostic tool to better understand where , how, what step wicth query etc its making the mess of loading data and tranformation: 

https://docs.microsoft.com/en-us/power-query/recordingquerydiagnostics 

 

if this helped please give some kudos, and if solved your question mark as solution for others to find. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.