Forum Discussion
Helper IVView Native Query always disabled
Are you adding a SQL statement to the box below? If so, that is why. Power Query cannot fold if you do that. If you want to do your own SQL plus have PQ fold more stuff, you need to create a view on your server and connect to that, or create ALL transformations in Power Query. Never use that box below. It also breaks incremental refresh and can cause security issues with gateways not being able to execute the manual statment.
You simply clear that box. Once you type in the server name and optional database name, you'll get a list of databases/tables & views to connect to.
Then start your transformations. You'll see folding happen for many things. Grouping, renaming, triming, even pivoting, custom columns, and nested if/then/else constructs in added columns.
Things like Text.Clean(), Text.Start/Middle/End will break folding. So save those things as late as possible. Query folding is an art form, not a strict science. For example, if you say = null, that will fold. If you say is null, that will break folding.
Post back to this forum if you need help with specifics.
Here is an image for the SQL connector after I typed in just localhost for my personal SQL Server. Just start adding tables/views.
Please mark this as a solution since we've fixed this key issue. We can work on other stuff in new threads as you run across things that break folding and we'll see if we can help unbreak it. Not always but sometimes.
And like I said, never use that box. If you have some crazy SQL that must be done, do it in a view, connect to the view as described above, and keep working. Power Query will fold on top of a view just like a table.
Community ChampionAre you adding a SQL statement to the box below? If so, that is why. Power Query cannot fold if you do that. If you want to do your own SQL plus have PQ fold more stuff, you need to create a view on your server and connect to that, or create ALL transformations in Power Query. Never use that box below. It also breaks incremental refresh and can cause security issues with gateways not being able to execute the manual statment.
Helper IVYes, we use that box 100% of the time. So, 'custom' SQL breaks, actually prohibits, folding?
How do we connect to a View/Table outside of it?
Also, how would we do ALL transformations in PQ w/out first getting source data?
- edhans
You simply clear that box. Once you type in the server name and optional database name, you'll get a list of databases/tables & views to connect to.
Then start your transformations. You'll see folding happen for many things. Grouping, renaming, triming, even pivoting, custom columns, and nested if/then/else constructs in added columns.
Things like Text.Clean(), Text.Start/Middle/End will break folding. So save those things as late as possible. Query folding is an art form, not a strict science. For example, if you say = null, that will fold. If you say is null, that will break folding.
Post back to this forum if you need help with specifics.
Here is an image for the SQL connector after I typed in just localhost for my personal SQL Server. Just start adding tables/views.
Please mark this as a solution since we've fixed this key issue. We can work on other stuff in new threads as you run across things that break folding and we'll see if we can help unbreak it. Not always but sometimes.
And like I said, never use that box. If you have some crazy SQL that must be done, do it in a view, connect to the view as described above, and keep working. Power Query will fold on top of a view just like a table.- edhans
By the way, the reason the written SQL breaks folding is Power Query needs to evaluate your entire transformation flow, including merging and appending of tables. Then it will work backwards to the source and generate a full and complete SQL Statement. It cannot do that if you already have SQL code at the source.
I've generated 800+ line SQL statements in Power Query with folding after doing lots of transformations and merging of tables.
- jcampbell474
Thank you for the guidance and explanation! I've seen the option to select a table/view in Dataflows. Never would have guessed that's how to find them in PBI.
Also, thank you for the detailed explanation on folding. Feels like there will be good bit of trial and error in the near future. 🙂
Edit: I do have one more question - Does the importance of folding primarily apply to transformations? Does the initial pull need to be sourced from a table or view, or will a custom SQL query perform just as well?