Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Suhel_Ansari
Helper IV
Helper IV

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

1 ACCEPTED SOLUTION

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-folde... 

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@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:

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

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




SH_SL
Frequent Visitor

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




SH_SL
Frequent Visitor

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete , Thanks 🙂 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.