We’re happy to announce Public Preview of the Query Plan visual for Power Query Online.
Recently we brought Step Folding Indicators into Public Preview, allowing users to understand better what operations are folding in their queries. However, this was just the beginning of the tooling we’re bringing to users.
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”..
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.
Moving forward
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 the Query Plan into Power Query Desktop, but we cannot provide a timeline at this point. We hope that this new feature gives 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.