Forum Discussion
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
Suhel
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:
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 --conditionsPete
-
8 Replies
Hi Suhel_Ansari ,
This isn't currently possible. Two DBs, even on the same server, are classed as two separate sources for the purposes of native query generation.
However, Append isn't a particularly expensive operation so, if you can get your two source tables to fully fold back to their respective DBs, the performance should be very good, even with millions of rows.
Pete
BA_Pete , Thak you for the prompt response, is there any M function that could help me with this issue.. ?
Regards
Suhel
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:
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 --conditionsPete
-
