Does PowerQuery Data Preview work differently than Data Loading in PowerBI?

Question

PowerQuery tends to load previews quickly but the very same data takes much longer to load into the PowerBI model when we click 'Close and Apply'. Is there some difference in the way these two things are done? They both depend only on a SQL server stored procedure and I cannot share any screenshots here due to confidentiality of company data. I am hoping someone else has had this issue and/or understands how PowerBI data loads work and can explain this difference.

Tried multiple data loads and varying the timeout period. I expected that lengthening the timeout period would make a difference but the load failed.

** I posted this question earlier today and got a pretty hostile reply and a down-vote so I deleted that one and tried to re-phrase it and repost.

Answer 1

PQ can work differently depending on the circumstances (flat files, query folding, transformations involved). PQ typically works by streaming data rather than loading an entire dataset in order to be more efficient with memory. Given you only preview 1000 records in the preview window, if no aggregations or sorts are happening, only 1000 records will be streamed so you can get a quick preview. When loading an entire dataset, then all records will need to go through the transformation steps rather than just the first 1000. This is a really in-depth topic and videos like the following give you some insight: https://www.youtube.com/watch?v=AIvneMAE50o

The following articles also explain this in detail:

https://bengribaudo.com/blog/2018/02/28/4391/power-query-m-primer-part5-paradigm

https://bengribaudo.com/blog/2019/12/10/4778/power-query-m-primer-part12-tables-table-think-i

https://bengribaudo.com/blog/2019/12/20/4805/power-query-m-primer-part13-tables-table-think-ii