Apply query changes hangs at 1000 rows

[Anonymous]

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.

[v-shex-msft]

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]

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.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Maybe you can take a look at following links:
Referenced Queries And Caching In Power BI And Power Query

How and When to use List & Table Buffer?

Regards,

Xiaoxin Sheng

[Greg_Deckler]

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".