Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Helper V
4 years ago
Solved

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 ...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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-folded 

     

    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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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's avatar
Suhel_Ansari
Helper V
4 years ago

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

Regards

Suhel

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    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-folded 

     

    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's avatar
      SH_SL
      Frequent Visitor
      3 years ago

      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's avatar
        BA_Pete
        Super User
        3 years ago

         

        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