cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Append Queries as New will result in no query folding

Dear fellow BI'ers,

 

I have a really strange behaviour when appending queries as new.

 

In my scenario, I have one source (SQL Server) to whom I connect. This source has several companies within it. Always with CompanyName$TableName as the table name. I then filter the tables to show me only a specific table. Lets say Sales. I will get a list of the Sales table from all companies. I will add those to my Queries with "Add as New Query". Afterwards, I will delete the filter step between Source and Navigation. Then, I will add a column called Company with the respective company name. Until now, I can see the native query.

 

If I then take the first query and click on "Append Queries as New" and append all of them together, the new Source will not be able to show me the native query. To confirm, that no query folding occurred, I will make diagnostics. This confirms that there is no query folding.

 

When I take the first query and click "Append Queries" and take exactly the same queries, query folding works.

 

This is new behavior that I haven't seen before. Can you help me how this happens and how this can be changed???

 

Thanks,

PowerNils

1 REPLY 1
Super User III
Super User III

Compare the generated M code between the two approaches. 

 

Append as new:

let
Source = Table.Combine({FirstTable, SecondDTable})
in
Source

 

Append :

#"Appended Query" = Table.Combine({ThisTable, OtherTable})

 

So - technically there's no difference.  Maybe query folding doesn't like to work with references only.

 

Having said all this - have you considered doing the UNION ALL at the source, ie with a custom SQL query?

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors