Forum Discussion
How to Append Works ?
Hi
I am using Append to join to SQL query. When I refresh data it is contacting the SQL server.
Example:
Query 1 -- Contacting SQL Server
Query 2 -- Contacting SQL Server
Append (Query 1 and Query 2) - Will this contacts the SQL Server again or will just do the join based on the data imported by Query 1 and Query 2 inside PBI Desktop?
The loading from SQL should always be just be the two tables.
If you Append then the actual appending will be pushed to sql (though not a new table load)
If you append them as new, then Power Query will do the heavy lifting of appending them since query folding is broken.
But in the end, just two tables are loaded from SQL. The question becomes is it better to have SQL append them, or have power query. I'd say generally want to keep as much on the server as possible (as they are built for this) but depending on the size and cardinality, PQ might be just fine. If you are concerned I'd just run some test and see what shakes out. The good thing is that these tables are only updated at refresh time, which you can set for off-peak hours.
5 Replies
if you append (not append as new) it will stay using SQL. You can make many transformations in Power Query which will in turn push those to SQL, but not everything can be done in sql. And once you do something in PQ that cannot be done in SQL the query folding is done and everything from that point one is done in Power Query. I cant rememeber what does and does not break the query folding, but if you look in the applied steps and right click a step if "View Native Query" is clickable you are still pushing everything back to SQL. If it's greyed out, then its all in power query:
if you append as new, then the view native query is greyed out.