Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Helper V
4 years ago
Solved

How to Achieve Power Query folding with Append Quires

Hi All, I have 2 Power Quires coming from 2 different SQL DB, the Query Folding breaks when I do "Append Query" for both please assist how can I achieve Query folding in this case. Thanks Regards ...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Suhel_Ansari ,

     

    No. As I said, two DBs, even if on the same server, are treated as two completely different sources for the purposes of query folding.

     

    You can check Microsoft's documentation on this topic here:

    https://docs.microsoft.com/en-us/power-query/power-query-folding#determine-when-a-query-can-be-folded 

     

    Within that article, you will see that they specifically describe the following as a "Transformation that prevents query folding":

     

    • Appending (union-ing) queries based on different sources.

     

    If you really want to do the whole operation server-side, I would recommend creating a view on one of your DB's referencing the other DB with a UNION clause, something like this:

    create or alter view
dbo.viewName
as
select column1, column2
from currentDBtableName
where --conditions
union
select column1, column2
from otherDBname.dbo.otherDBtableName
where --conditions

     

    Pete

SH_SL's avatar
SH_SL
Frequent Visitor
3 years ago

Thank you so much that's a great answer!

On a slightly other Topic: Sometimes i wonder if i should do the ETL process with the select SQL Code instead of the Power Query Editor. I found only very few ressources on that topic so far. Do you maybe have some links i could read up on?

BA_Pete's avatar
BA_Pete
Super User
3 years ago

 

I don't have any links on this topic as it's a really big "it depends" topic.

Personally I never use SQL Native Queries from Power Query. I write views on the server to do as much heavy-lifting as possible to get the data into a generic shape, then I bring the view into Power Query to make further report-specific transformations.

Views still support query folding, so this leverages SQL Server transformation power while maintaining reusability at a high level.

 

Pete