Number of rows loading into Power BI after completing Query steps much higher than the real number

Hi Anonymous ,

This is due to your code requiring your table to be scanned multiple times to achieve the result you want.

By referring back to the table itself in order to pick up the previous row, you are requiring Power Query to load that table again so that it can be scanned to find rows in itself that meet your matching criteria.

For example, let's say your table starts at 6M rows, then your create a filter step called 'filterStep' to bring it down to 200 rows. In your next step where you want to find the previous row values (let's call it 'prevRowStep'), you refer to 'filterStep' as the table you want to grab your previous row values from. Here's what Power Query is doing:

1) Source = Import 6M rows

2) filterStep = Filter to 200 rows

3) prevRowStep = Get previous row values from filterStep table, therefore has to reload up to filterStep:

    3a) Source(temp) = Import 6M rows

    3b) filterStep(temp) = Filter to 200 rows

    3c) prevRowStep = Apply comparison criteria between filterStep and filterStep(temp) to display output

This is likely simplified versus what you are actually implementing (e.g. additional 'if' statements etc.), but you can already see that when you apply this structure to the data model, you are essentially loading 12M rows in order for this transformation to be applied. The 'if' statement itself doesn't reduce this at all, because it has to load all the data to enable the 'if' comparison to take place. It can't just discount rows that don't meet the 'if' criteria without knowing what's in them i.e. loading them.

Some things you can do to help:

1) If you are working on an SQL Server or another 'foldable' source, ensure that your query steps are folding right up to the filterStep query step. This will allow PQ to offload the initial steps to the server so it's only working with two tables of 200 rows for this last transformation (the server will do the 6M > 200 bit, so PQ doesn't have to).

2) You MAY find some mileage in using Buffer functions (Table.Buffer, List.Buffer etc.) in order to put a static version of your query into memory before performing futher transformations on it. PQ can then just refer to the buffered table rather than keep going back to the source to reload it each time you reference it. Without seeing your code, I can't definitely say whether this would work for you or not, but it's a fairly standard way of limiting source scans in PQ.

Pete