How to Achieve Power Query folding with Append Quires

[Suhel_Ansari]

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

[BA_Pete]

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

[Suhel_Ansari]

@BA_Pete  , Thak you for the prompt response, is there any M function that could help me with this issue.. ?

Regards

Suhel

[BA_Pete · 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

[SH_SL]

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?

[BA_Pete]

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

[SH_SL]

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]

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

[Suhel_Ansari]

@BA_Pete , Thanks 🙂