Query Folding inconsistencies

[uberdube]

Hi,

Yet another head-scratcher here... I am trying to test query folding on two different queries in Power Query (Power BI Desktop). Although the tables and conditions themselves slightly differ, I am still running exactly the same PowerQuery applied steps on each table (they both contain a Merge Queries step with a left join,  yet Query Folding only works on one of them and not the other?!

Both queries are initially connecting to views in the same on-premise SQL Database source.

I've followed the known workaround for Merge Queries here , and am even using Left Joins instead of Inner Joins, giving it even more reason to be successful (I understand Left Join is the best 'supported' join for Query Folding as Inner Joiins are problematic - can anyone verify if this is still the case?)

This is making a relatively simple activites into a grind trying to troubleshoot something that appears like it should work consistently, It's not obvious to me what's causing this if something is different in the background.

Out of interest - I have also noticed sometimes that when first hovering over 'View Native Query' it's greyed out, but if you click back and forth over previous steps and give it a while, sometimes it actually appear as valid again?? I've seen this sometimes when the 'preview' hasn't finished loading, but other times it just happens randomly.. Has anyone else experienced this odd behaviour? 

Any help appreciated!

[rdg515]

I'm going to add this also... the database you specify for both data sources you're trying to merge must be in the same case.  I was trying to figure this out and finally realized my problem was Db.Table1 vs DB.Table2.  Once I made them the same case query folding worked.... sigh*

[edhans]

A couple of thoughts:

1. Move your expansion step to be immediately after the merge, then do the remove columns. I've found that by not expanding immediately after the merge, it can break folding. Not will break. Can break.
2. Are the data types of the merged columns the same in both scenarios? Text to text, integer to integer, number to number, same precision if a number, etc. etc. PQ generally won't let you join unmatching columns (text to integer) but not sure how rigerous it is on a numeric field that is 9,3 precision to a field that is 9,4 precision for example.
3. If those fail, can you post the M code here to both?

[uberdube]

Hi @edhans thanks so much for taking the time to look at this. I tried your suggestions to no avail, however I think (yet again 😆) I may've answered my own question..

What I discovered was that for the query that wasn't folding correctly (regardless of all the transformation/applied steps that follow) the query I was trying to merge with was actually pointing to a different SQL database as the source (my bad, sorry!). The one that folded correctly was pulling the same data, but from a source on the same SQL database.

So it appears that query folding will only work if it is all being conducted within the same database (I can only speak for SQL Server, not sure if this would be the same for other sources), as it appears to me that Power Query isn't able to implement query folding on cross-database joins, as is possible to do in a written native SQL query (please correct me if this is wrong?).

Other observations:

1) I found it perfectly ok to have one (or many successive) reference/s to another query/s as a data source, and have query folding still work successfully, as long as the referenced query/s still ultimately point to the same database as a data source, as a starting point somewhere down the line.

2) Query folding will work for Inner Joins, however (again correct me if this is inaccurate) if creating Applied Steps one by one - it appears you must actually allow Power Query to import the entire datasource rows the first time when doing an inner join merge, to enable you to then create the 'Expand' step, which re-introduces the query folding. If using a predefined M Query this doesn't appear to be an issue however because the subsequent 'expand' step already exists.. and of course the query still has to be written to ensure fold happens correctly). Left joins don't seem to need to import all of the data initially, they just seem to identify as 'foldable' straight away without needing to import the data first (I still don't quite understand why it would be different for an Inner join, if anyone knows? But happy to accept that's just how it is 😆).

I feel like this was a bit of a rookie oversight haha, but what can I say, I'm still on the rookie side of things 😉 I actually learnt a lot from this however so that's a good thing.. hopefully this will be useful to others as well.

Cheers 😁

[edhans]

A few comments:

Yes, natively, Power Query cannot fold a query across databases. However, there is a workaround that I use for this. I create a view in Database1 to the relevant data in Database2, then pull everyting in Power Query from Database 1. If you have access and permissions to create the views, use the following code in SSMS for a new view in your main database, Database1 example:

CREATE VIEW [dbo].[tablename]
as
select * from Database2.dbo.tablename2
go

Then join to the new tablename in our query.

When writing actual SQL statements, you can reference other databases and even other servers if configured properly, but Power Query doesn't support folding for those cases. Only within the same database, but since that includes views, you can accomplish much of the same.

I'm not sure what you mean by a pre-defined M query. You may be talking about the Relationships feature in SQL server where whn you pull a table, it automatically shows you related tables to optionally expand. That is a feature of SQL server, not Power query, but PQ supports it. Those don't need to be expanded to preserver folding. You only must expand manual joins in Power Query - all of them - inner, right, left, anti-right, anti-left, and outer - or you risk breaking folding with the next step.

[uberdube · Accepted solution]

Awesome thanks for the great information there - and as a matter of fact I did actually create a 'view' of the other datasource (as you suggested) when the penny dropped after my last reply.. so great to know my thinking is sound then! 😄

Apologies for the confusion there about using the phrase 'pre-defined M query' - all I was getting at there was that if I am 'manually' building my M query using sequential applied steps, I have to wait for the entire data import to happen on Inner Join merges, before I can move onto creating my subsequent steps (ie. the Power Query tools are not 'responding' while the data is importing, so can't move onto the next step of Expanding the merged table query, deleting columns etc..), and 'View Native Query' will be greyed out until the load is finished.  However if I was to then take a copy of the actual M query that sequence of steps  generates (from the Advanced Editor window - this is what I meant by saying 'pre-defined')  and dump it into a new query Advanced Editor window, then the correctly-folded query already exists... so 'View Native Query' can be checked instantly at the final applied step, without having to wait ages for data to load for the 'inner join merge' step which sits midway through (I'm working with millions of rows tables here so the import wait time factor is significant to this observation).

I hope that makes more sense now - and again this is just what I've observed through my own experience 🙂

Thanks again for your great help!