Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Refreshing Error Due to Insufficient Memory

Hi All,

 

I have a PowerBI report that is pulling in data from an excel spreadhseet that has around 5000 rows with 15 columns. Certainly I had to perform a lot of transformations and calculation to generate the report that was asked for. It was loading properly until the recent August update, but I am not sure if this is the issue as I have made some more changes. 

Note: I am referring to a table in various queries as source table  to perform other tasks, process it in a different way and I can see it is taking upto 25GB of RAM in the task Manager, but still failing. Please let me know your suggestions on this. Thank everyone in advance.

General Comment
Interesting Links
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    Quite a lot going on in these queries.  I think you will need to spend some time looking for optimisations.  Some code rows that stood out to me.

     

    In RAW:

    #"Add Col Tables promote headers" = Table.AddColumn(#"Removed Columns1", "GetExcelPromoteHeaders", each Table.PromoteHeaders([Data])),

    That looks like a piece of code that has potential to blow out.  I'm unsure what the exact reason to do this row by row is?

    In RAW:

    #"Invoked Custom Function" = Table.AddColumn(#"Remove old no header tables", "AddColSheetName", each AddColSheetName([GetExcelPromoteHeaders], [Item])),

    Check that this function isn't causing you issues.  Its hard to say from the outside.

    In the other query:

    #"Added Custom" = Table.AddColumn(Source, "Custom", each #"Submit Date"),

    This stood out to me as being a strange item.  You are attempting to get a single date, but seeming to be joining a whole table on each row?

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous,

    Firstly, do you use 32 bit or 64 bit Power BI Desktop? Switch to use 64 bit Power BI Desktop if it is possible.

    Secondly, turn off the following options in your PBIX file.


    Thirdly, do you need to load all the tables to Power BI Data view? You can consider to disable "Enable load" option for some bridge tables.



    At last, how much RAM on  your machine? Do you run other applications at the same time?

    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you for the comments. 

       

      1. I am using a 64 bit version of PowerBI

      2. I have disabled the mentioned options already.

      3.  I have a virtual maching with 25GB of RAM and using it only for this report.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Just a double check on your Excel file.  You have stated its only 5000 rows.  When you open it in Excel, if you were to grab the scroll bars and manually drag it to the bottom, does it stop at the 5000 rows, or does it take you down well into the blank spaces?

         

        A common issue in excel is to track the "Used Range".  Conceptually your file might be 5000 rows, but Excel might have had a reason to believe your file is much longer and larger.

         

        The solution to this problem, if its occuring, is to scroll to the last row and selected all of the back rows (row 5001 until the bottom) and then select "Delete Rows".  Saving the workbook after doing this should shrink the size of the workbook backdown.

         

         

        The Same problem can occur with the columns.