Step Folding Indicators and Query Plan for Power Query Online
Step Folding Indicators
One of the most powerful features in Power Query is the ability to take relational operations – things like grouping, filtering, etc. – and pass to the data source the ones that it knows how to handle. We call this ‘folding’, and it allows users to be able to avoid retrieving data that they don’t want to, speeding up processing immensely.
A common ask that that we’ve received for some time is a way to understand what folds, what doesn’t, what breaks it, etc. We laid some of the groundwork for this in Desktop last year with Query Diagnostics, but it still required expertise to understand the output. I’m pleased to share that we are now previewing step folding indicators in Power Query Online. These indicators allow you to understand which steps fold, and which steps don’t. When you make a change that breaks folding, it will become obvious. This allows you to resolve issues more quickly and easily, avoid performance issues in the first place, and have better insight into your queries.
Using the Northwind sample database, I’ve connected to the Products table and loaded data. If you look at how this shows up in step folding indicators, you can see that the first step doesn’t fold, the second step is inconclusive, and that the third step folds.
You can see that the initial steps don’t fold, but the final step generated when you load data initially does fold. How the first few steps (Source, sometimes Navigation) are handled depends on the connector. With SQL, for example, it’s handled as a catalog table value, which doesn’t fold. However, as soon as you select data for that connector it will.
Conversely, this can also mean that your query folds up to a point and then stops folding. Unlike in the case where you have a “folding” indicator for the step, which shows that everything folds, when you have a “not folding” indicator it doesn’t mean that everything doesn’t fold – instead, it means that “not everything” folds. Generally, everything up to the last folding indicator will fold, with additional operations happening after.
Modifying the example from above, you can give a transform that never folds – Capitalize Each Word. In step folding indicators, you will see that you have the exact same indicators as above, except the final step doesn’t fold. Everything up to this final step will be performed on the data source, while the final step will be performed locally.
Step folding indicators use an underlying query plan, and require it to be able to get information about the query to report it. Currently the query plan only supports tables, so some cases (lists, records, primitives) will not report as folding or not. Similarly, constant tables will report as opaque.
Query Plan Visual
The Query Plan visual will allow you to understand what’s happening under the hood of your query with more details than the Step Folding indicator. While Step Folding indicators provide a “check engine light” for every Power Query user, the new Query Plan provides a much more detailed view for advanced users. Similar to how SQL Server’s “” capability can let you see the optimized form of the Query Plan in Power Query Online will let you see how the Mashup Engine will process your query.
You will be able to understand what operations don’t fold, rather than relying on seeing which steps don’t fold. For some connectors, such as OData and ODBC, the query plan will include multiple alternate plans. A decision to follow one path or another will be determined on actual query execution. These paths are denoted within the Query Plan through nodes. The step indicator for plans that include Value.Alternates where at least one of the paths folds is the “Might fold” indicator above.
Connectors that currently support “Datasource Queries” will display such queries in the plan – simply because this isn’t displayed in the plan doesn’t mean that a query isn’t folding, however, as not every connector supports “Datasource Queries”.
Accessing the Query Plan
You can open the Query Plan by right clicking on a Step in the Steps Pane.
Note that steps with some indicators, such as the ‘Opaque’ on Navigation 1 above, do not support the Query Plan. It will be greyed out. You can read more about types of indicators in our Step Folding Indicators documentation.
When you select ‘View query plan (Preview)’ it will open a new modal displaying the Query Plan visual (depicted above).
When read left to right, the query plan roughly corresponds to how M script is written. However, when the engine executes a sequence of functions, it will start with the right most node. Each node will sequentially call the node to its left, whose results it depends on.
For connectors that currently support “View data source query”, the data source query will appear as a node right to the right of the data source node, under a Value.NativeQuery heading. This should merely be considered as a way of representing the data source query in the Query Plan, and not as an indication that Value.NativeQuery is supported for the data source.
In the case where all operations folded, you would only see one or two nodes:
- NativeQuery (omitted in some cases)
- Datasource function or Resource.Access node..
Nodes can be selected to show more information. Query operations are represented by the closest Power Query function, and include parameters for the function invocation that will help disambiguate complex query plans where a single operator is used repeatedly.
For now, this feature is primarily useful for identifying at a deeper level what operations are getting broken off for folding, as well as what connectors are emitting. In many cases, viewing the query plan can assist in reordering steps to allow the query to fold into the data source. However, it lays the groundwork for much greater functionality. It is our goal that you’ll be able to understand after running a query how much time was spent in different nodes of the query plan, as well as how many rows were processed, so you can garner insight into rearranging operations or parts of your ETL pipeline to minimize load.
As with many other recently introduced Power Query Online capabilities, we are also working towards bringing both Step Folding Indicators and the Query Plan into Power Query Desktop, but we cannot provide a timeline at this point. We hope that these new features give you deeper insight into understanding and debugging queries in Power Query Online and look forward to delivering more capabilities. We appreciate your feedback and usage based on Power Query Online so we can continue evolving this new capability in parallel to enabling it in other products.
The Power Query team is interested in your feedback! Feel free to leave a comment below with your thoughts.