Enormous load activity for small Excel file

[TheBoojum]

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?

[v-deddai1-msft]

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]

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

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.

[StefanoGrimaldi]

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

[v-deddai1-msft]

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]

Thanks Stefano.

I'll give that a go and report back.

[StefanoGrimaldi]

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.