cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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

 

 
1 REPLY 1
v-yulgu-msft Super Contributor
Super Contributor

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 219 members 2,229 guests
Please welcome our newest community members: