Using Table.View to Implement Query Folding
This content is still under development. Entries may contain partial content.
One of the most powerful capabilities of Power Query and the M Language is Query Folding (also referred to as query delegation, and predicate push-down). Query Folding allows the M Engine to push the transformations expressed in an M query to the source, in the source's native query language, resulting in more efficient data processing.
Data sources will support different levels of query capabilities. To provide a consistent data transformation experience, the M engine compensates (i.e. does the processing locally) for transformations that cannot be sent to the source. It is the Data Connector's responsibility to report its capabilities to the engine, carving off the transformations it can handle, generating the appropriate query syntax, and letting the M Engine handle the remaining work. Data Connectors can implement query folding behavior through the
Table.View is used to override default handlers for query operations. Most handlers are optional, allowing a source to implement folding for the operations it supports. If a handler encounters a query expression it cannot handle, it can raise an error to have the M engine fall back to its default operation handler.
The sections below describe the handlers supported by
The following handlers are required for any implementation of
||(Required) Returns the table result. Always the last handler called.|
||(Required) Returns the M
GetRows handler returns the result of your data source function (i.e. a table). It is the final handler invoked, and should take into account all of the query state info set by the other handlers.
GetType handler returns the M table type of the result of the call to
GetRows. In the most basic implementation, this handler would call Value.Type() over the result of
Sources that can determine the schema of the result without evaluating the query (by using fixed metadata, or querying a metadata/schema service) would perform those operations in this handler.
Note that returning
Value.Type(GetRows())will result in
GetRows()being invoked twice. If the type cannot be determined without invoking
GetRows(), it is recommended the results are stored in a common variable. Please see the sample below for an example.
The following handlers can be implemented without handling M
RowExpression values, and are generally easier for an extension to implement.
||Called when as a result of
||Called when renaming columns (
||Called when selecting specific columns.|
||Called when using
||Called when table is sorted (
||Called when limiting the number of rows being retrieved (
Returns a number. The default handler for the
GetRowCount operation would be to call
GetRows. Override this handler if your source is able to calculate the total row count without evaluating the query.
Receives a list of column names. The handler must ensure that rows with duplicate values for the specified columns should be removed (i.e. remaining rows are distinct). See Table.Distinct.
Receives a list of lists, the same as the arguments to
Table.RenameColumns. Each inner list has two
text members - the first member is the old column name, and the second member is the new column name.
Receives a list of column names that the user has selected. See Table.SelectColumns.
Receives a number indicating the number of rows that should be skipped from the result set. See Table.Skip.
Receives a list of records of type:
type [ Name = text, Order = Int16.Type ]
Name is the name of the column, and
Order is equal to
Order.Descending. See Table.Sort.
Receives a number indicating the maximum number of rows that should be returned from
GetRows. See Table.FirstN.
The following handlers require processing M
||Called when adding a calculated column (
||Called for various aggregation transformations.|
||Called when performing a join of two tables.|
||Called when selecting rows based on an expression (
Direct Query Handlers
The following handlers are required to enable Direct Query capabilities from an extension. Note that an extension can implement query folding without declaring full Direct Query support. Direct Query support should only be enabled if the majority of the Table.View handlers are implemented.
||Called to determine Direct Query capabilities.|