Power Query: Power Query Online – Part 10

Welcome to our Power Query blog.  Today, I look at the query folding features in the diagram view in Power Query Online.

In the current series, I am looking at Power Query Online, which I have accessed from Power Apps:

Last week, I looked at the effect of marking a column used in a merge as a key.

I marked the column 2-alpha code as a key in both tables I planned to merge:

Then, I went back to the data view, and refreshed the query Merge again:

I found that the time had improved:

This week, I am going to look at a different dataflow.  I have some data that I have imported from an Azure SQL database:

For this table, although I only have one ‘Marked Key’ step, I have two separate keys indicated:

AddressID was already marked as a key in the database, and in Power Query Online, I am now able to see this information easily.  The icon is the same as the icon on City, which I have marked as a key myself.

Note also the query folding indicators in the ‘Applied steps’ section:

The query folding starts at step ‘Source’ and ends at step ‘Inserted Text Between Delimiters’.  If these icons do not appear, the setting can be adjusted from ‘Options’ on the Home tab:

The setting ‘Enable query folding indicators’ can be enabled from this dialog.

If I select the diagram view, I can see more information about what the query folding indicators mean.  I select the ‘Navigation 1’ step and right-click.

On the right-click menu, I have the option to ‘View data source query’:

This is showing me the SQL code which is used to extract the data from the database for this step.  This makes query folding easier to understand:  folding a query means that the steps so far can be expressed as an SQL statement.  If I move onto the ‘Choose columns’ step, and ‘View data source query again’, I get the SQL statement up to and including that step:

This time the SQL statement is shorter, because I have reduced the number of columns that I am extracting from the table.  If I try to view the data source query by right clicking on the step ‘Inserted Text Between Delimiter’:

The option is greyed out because this step cannot be incorporated into the SQL statement, as shown by the query folding indicator next to the step:

The M code in this step could be expressed in SQL, but not in the same statement as the previous steps, therefore the query folding has been terminated at this point:

These new features help to demystify query folding, especially for new users of Power Query.

Come back next time for more ways to use Power Query!