Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Apply query changes hangs at 1000 rows

I have worked around this issue for some time and finally broke it down to its smallest component.

I have a table with 1.8m rows that I refresh once a week from a Netezza\DB2 source but do it with ODBC\DSN as there is an encryption requirement that I can not get past with the Netezza connector.

In the most simple form I can refresh the 1.8m rows into PBI Desktop in 10 minutes or less. 

However once I had any sort of filter or other steps in the Power Query the table will say "Evaluating" for an hour or more then load the first 1000(sometimes 1004 or 1008) rows from dsn.

I am able to reproduce this issue even more simple.  Load the  1.8m table, then in Power Query just reference the orginal table into a new table...  "Apply query changes   1,000 rows from dsn=abc64"  with a spin indicator.  No filters or steps.  Only query is:

let
    Source = V_Support_Table
in
    Source

 

Not likely a resources issue on a 32GB machine with Win10 and PBI cache set to 12GB only using 4GB in PBI cache  8GB total used in system.

What is it evalating then pausing at 1000 rows to load an already cache loaded table?  Notice there are many steps in the query application process but have not seen a breakdown of each phase in PBI desktop.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous,

     

    Did you apply an advanced operations in your query tables?(e.g append, combine, reference other query, invoke custom functions...)

     

    If this is a case, it may caused the duplicate memory spend on calculation and will hang 'apply' operation and keep loading data until finish calculations.

     

    I'd like to suggest you try to use Table.buffer/List.Buffer to loading these resource which used in advanced operation to memory to avoid duplicate memory cost

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      In looking for proper M langauage on Table.Buffer I never found an example I could mimick.

       

      However I took a more serious look at the query folding and updated my select statement rather than handling in ETL steps.

      By selecting only the columns I was intending to use and note later removing them in step 2...the query went smother.

       

      If anyone has a good example of using Table.Buffer specfically for merges I would be interested.

       

      As well, I unpivot a fact table into another table by using the orginal fact table as a Reference Source..  I would think that be optimal as it is already and only need a few more steps for some aggregate measurements.