Forum Discussion
How to Achieve Power Query folding with Append Quires
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
-
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
Thank you for the answer.
I'm in a similar situation and was now wondering, if it would be better to do my queries i did after the append and without query folding seperately on the two tables before i append them?
-
