How to Append Works ?

[Anonymous]

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?

[Anonymous]

Looks like as long as no transformations were done that breaks query folding, the appending gets pushed to sql server. I imported two tables from the WorldWideImported SQL Server database:

But if you Append as New, then that breaks the query folding and the combining is done in Power Query

[Anonymous]

So do you mean to say. The new Append will again become an independent query and will contact the SQL server for new data rows or any changes?

[Anonymous]

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. 

[Anonymous]

Thanks and thats a great info.. What I am actually trying to ask is...

I am concerned about the load going to my DW server due to Data Refresh in PBI Desktop. If i am having two independent query the two query will contact the DW server for data periodically. SO now the load is two for every refresh.

If I am appending these and creating a new one. Then when I refresh the data the load will be 3 or still 2?

I mean the appended new query will again contact my DW for data changes or will not?

Kindly help to understand.

[Anonymous · Accepted solution]

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.