Forum Discussion

BrunaSeixas's avatar
BrunaSeixas
Frequent Visitor
8 years ago

Memory Consumtion & Power Query Processing (time)

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, pivo...
Need Help
BrunaSeixas's avatar
BrunaSeixas
Frequent Visitor
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    BrunaSeixas
    Frequent Visitor
    7 years ago

    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,