Memory Consumtion & Power Query Processing (time)

[BrunaSeixas]

Hello, 

I would like to know if someone face the same issue as me. Recently I´ve created a PBI file in which I do some ETL on the Power Query, the applied steps are basically replace values, pivot, merge with other tables and new calculated columns (many of them). At first we were using as data source an excel file with 600 k lines (50MB), as we were not able to process it and the process on the Power Query never ended (we let it running for at least one day), we decreased our data source to 30 lines (150KB) and it still does not run and now the computer even turn off the process saying there is not enough memory to process it. 

What could be the problem??

(My notebook system: Intel Core i5 - 4300M CPU 2.6GHz, 64bit, 8GB; I really think this should not be the problem)

[Anonymous]

Hi @BrunaSeixas,

This issue which appears when you invoke/reference other query table to calculate with current contents.

You can consider to modify your formula with List.Buff and Table.Buff function to place invoked table/list to memory to reduce memory spend on looping calculate.

year to date in query editor

Regards,
Xiaoxin Sheng

[BrunaSeixas]

Hi,

I will try that. One thing I realized when trying to solve the problem was that the number os "future" applied steps may be an issue either. The thing is:

1. My applied steps are (in that order):
   1. Source = Excel.Workbook(File.Contents("C:\Users\eztoubr\Documents\Mathias SOS\2017 Q1 test.xlsx"), null, true)
   2. #"2017_Sheet" = Source{[Item="2017",Kind="Sheet"]}[Data],
   3. #"Promoted Headers" 
   4. #"Changed Type" 
   5. #"Renamed Columns" 
   6. #"Added Conditional Column" 
   7. #"Reordered Columns" 
   8. #"Removed Columns" 
   9. #"Merged Queries" 
   10. #"Expanded Label Country" 
   11. #"Reordered Columns1" 
   12. #"Removed Columns1" 
   13. #"Renamed Columns1" 
   14. #"Inserted Merged Column" 
   15. #"Merged Queries1" 
   16. #"Expanded Label Opco" 
   17. #"Removed Columns2" 

   18. #"Expanded Operator Group

   19. "Renamed Columns2" 

   20. #"Merged Columns" 

   21. #"Merged Queries3"

   22. #"Expanded IDC KPI Compilation" 

   23. #"Reordered Columns3" 

   24. #"Removed Columns3" 

   25. #"Renamed Columns3" 

   26. #"Inserted Merged Column1" 

   27. #"Inserted Merged Column2"

   28. #"Reordered Columns4" 

   29. #"Reordered Columns5" 

   30. #"Removed Columns4" 

   31. #"Pivoted KPIs" 

   32. #"Added Fixed Total TV Subscriptions" 

   33. #"Added Fixed IPTV Subscriptions Convergence" 

   34. #"Added Mobile Total CDMA Subscriptions inc others" 

   35. #"Added Mobile Total CDMA Subscriptions" 

   36. #"Added Mobile Total Subscriptions inc others"

   37. #"Added Mobile Total GSM Subscriptions" = 

   38. #"Added Fixed Total Subscriptions" 

   39. #"Added Mobile Total Net Additions" 

   40. #"Added Mobile Total Subscriptions" 

   41. #"Added Mobile Services Subscriptions"

   42. #"Changed KPIs Data Type to Decimal"

2.  I run a smaller version of my data and the refresh of all those applied steps stops exactly at the first merge (step 9)
3. When I remove all the applied steps from step 19 to 42, suddenly the refresh is made until the step 18

Any clue why is that? It seems like the numberof applied steps affects my refresh which is completely strange. 

[Anonymous]

HI @BrunaSeixas,

As I said, invoke or refer other steps in query edit may caused additional memory cost(row count * referenced part records) on reference data when you apply change to data view.

For this scenario, I'd like to suggest you use Buffer functions to move reference part to memory to reduce memory consumption.

Regards,

Xiaoxin Sheng

[BrunaSeixas]

Hi, 

Sorry for the late reply, but we did that and it did not seem to work so well. We decide to make our ETL using the Power Query on excel and it processed much better. 

Thank you for the help anyway. 

KR,