Why does Power Query repull gigs of data for the smallest change?

[Kirt1965]

Hello... Can someone explain why Power Query forces a refresh for the simplest things?  A case in point: It is forcing the pull of several gigs of data (seen in the image below) when all I did was tell it to sort the 18 result rows on the TcID column.  I could kind of understand if I had inserted that step in the middle of other steps, but that's not what I did.  I added the sort as the last step after the 18 rows were already returned.  Why does it force a complete refresh pulling gigs of data when it already had the 18 rows I wanted it to sort?  It does this kind of thing all the time and, frankly, it seems idiotic.  It should be able to retain what was already there with me having to wait long periods of time for a change like this.

[v-stephen-msft · Accepted solution]

Hi @Kirt1965 ,

When you refresh data in Power BI, it performs a full refresh and not an incremental one. This means that whenever you refresh data in a dataset, Power BI uses information in the dataset to connect to the data sources defined for it, query for updated data, and then loads the updated data into the dataset. Any visualizations in your reports or dashboards based on the data are updated automatically. If your dataset is configured for a live or DirectQuery connection, datasets are refreshed approximately each hour or when interaction with the data occurs. It is by design behavior of Power query to send multiple queries, one to find the schema and the other query to get the actual data.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[ppm1]

Two things:

1. Confirm that the data preview in the background box is turned off. That is a global setting but since your file is already in progress, you may need to turn it off in the Data File section of options too.

2. Sorting can be an expensive operation as the whole table has to be loaded into memory. #1 should address the issue but if not, consider a different way to get same result.

Pat