How does Power Query work exactly? Does it run the Native SQL, or does it perform all steps?

[Anonymous]

So I have a few quite big tables, of which I require only a subset of data. I made extensive use of Power Query's append and merge functions to combine two tables and then reduce the data sets. When I look at the Native Query, it seems exactly what I'd need to pull only the data I need. When I load the data though, it seems that Power Query first fetches all the rows, before applying the reduction logic. This seems to be a bit against the point of speeding up the loading of data. Am I misunderstanding the concept, or am I doing things wrong?

To give you an example:

Step 1/ Load table Customers country A

Step 2/ Load table Customers country B

Step 3 / Append steps 1 and 2

Step 4 / Merge the combined Customer Query against another (Sales) table to only keep customers that bought someting in 2018

While step 4 should only be around 10% of the total customers, Power Query seems to be pullling in the entire set, before applying the logic.

One additional question: If I load both tables from step 1 and step 2, could I disable the "enable load" on one of the customer tables, since this logic is already in the Append action on the first table? Or do I need this active as well to update the data in the first table?

Thanks

[v-yulgu-msft]

Hi @Anonymous,

---

---

When I load the data though, it seems that Power Query first fetches all the rows, before applying the reduction logic. 

Yes. Power Query works that way. It loads all data from data source. However, for some specific data sources, like SQL Server, you can input "SQL Statement" under advanced option when establishing connection, in order to avoid all data rows into desktop.

---

 One additional question: If I load both tables from step 1 and step 2, could I disable the "enable load" on one of the customer tables, since this logic is already in the Append action on the first table? Or do I need this active as well to update the data in the first table? 

---

Yes, you can disable the "enable load" on one of the customer tables. "Enable Load" means query results are available for report view. Disabling this option makes current table invisible in report view, but the update data in this table would still be shown in merged table.

Best regards,

Yuliana Gu