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 ,
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 --conditions
Pete
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?
I would do all foldable transformations on each query BEFORE appending.
An SQL Server will chew through most transformations far quicker than Power Query will, so you'll get further through the overall transformations faster. For maximum efficiency, you may need to be smart about the order of your transformations in order to maintain folding for as long as possible.
Once you get to a point that you can't fold to the source any more, then do subsequent transformations on the appended query. This will give you processing synergies by only performing the transformation once (albeit on a larger dataset).
For example:
-- Sources --
TableA (TA)
- Transformation step TA1 (Foldable)
- TA2 (Not Foldable)
- TA3 (F)
- TA4 (F)
- TA5 (NF)
= Desired output
TableB (TB)
- Transformation step TB1 (Not Foldable)
- TB2 (NF)
- TB3 (Foldable)
- TB4 (NF)
- TB5 (F)
= Desired output
-- End Sources --
-- Optimal Process --
TableA (TA)
- TA1 (Foldable)
- TA3 (F)
- TA5 (F)
TableB (TB)
- TB3 (Foldable)
- TB5 (F)
APPEND
TableAB
- TA2 (Not Foldable)
- TA5 (NF)
- TB1 (Not Foldable)
- TB2 (NF)
- TB4 (NF)
= Desired Output
-- End Optimal Process --
Hope this makes sense.
Pete
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?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