Forum Discussion
Query Folding inconsistencies
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!
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
goThen 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.
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!