You’ve built a custom connector—and it works great! But regardless of how good your query folding implementation is, likely there are times that a hand-crafted native query can beat it in performance and capability. So, in addition to supporting query folding, you’d like to give users the option of using native requests that they write with your connector.
In a nutshell, you’re hoping to enable something like:
let
Source = MyConnector.DataSourceFunction("some-host"),
Results = Value.NativeQuery(Source, "some native request goes here")
in
Results
Is this possible? From Power Query’s perspective, at the technical level: Yes—and it is easy to pull off.
Handler OnNativeQuery
The standard library function Value.NativeQuery folds to handler OnNativeQuery. In order for your custom connector to handle native queries, its Table.View simply needs to implement handler OnNativeQuery.
OnNativeQuery(query as text, optional parameters as any, optional options as nullable record) as any
Except for target, Value.NativeQuery‘s parameters map directly to those of OnNativeQuery.
Value.NativeQuery(target as any, query as text, optional parameters as any, optional options as nullable record) as any
When Power Query encounters a foldable Value.NativeQuery call where the target parameter is your source, it invokes your table view’s OnNativeQuery. This handler is passed all the arguments that were passed to Value.NativeQuery, except for target, which isn’t needed since your view already knows that it is the target.
Your OnNativeQuery implementation should then respond with whatever is appropriate for the given native query (return a table of data, return a scalar value, etc.).
For demo purposes, we can skip the custom connector altogether and both create and consume the table view directly in Query Editor:
let
Source = Table.View(
null,
[
GetRows = () => #table({"A"}, {{1}}),
GetType = () => Value.Type(GetRows()),
OnNativeQuery = (query as text, optional parameters as any, optional options as nullable record) as any =>
if query = "Return Secret Code" then 123
else ...
]
)
in
Value.NativeQuery(Source, "Return Secret Code") // returns 123
The OnNativeQuery handler receives a native query request with its parameters and options. Then, it decides what to do with those values (in real life, presumably passing the relevant portions to the upstream source), then returns the appropriate results.
Details
Options
On options, you decide which options your connector supports. Power Query will simply pass to your handler the options record that the was passed to Value.NativeQuery.
Options are connector-specific: there’s no predefined list of options that all connectors must support or constraints on what options you can support (as Value.NativeQuery‘s docs put it, “These options are specific to target.”). In a simple scenario, you might not choose to support any options.
Whatever options you choose to allow, it would be good manners to validate what’s passed in, erroring if an unsupported option is used.
For example, here’s how Sql.Database responds to an unexpected option:
Errors
Raising an error brings up an interesting situation:
If an error is raised by a Table.View handler, Power Query falls back to the default behavior for the requested operation. For example, imagine that Power Query attempts to fold operation Table.RowCount by invoking handler GetRowCount. If that handler raises an error, Power Query will skip folding it and install fall back to having Table.RowCount enumerate the table’s rows and count them itself.
The fact that an error triggers a fall back to the default behavior leads to the question: What is the default behavior of Value.NativeQuery? If Power Query attempts to fold Value.NativeQuery to view handler OnNativeQuery and receives back an error, what happens?
Value.NativeQuery dies complaining that “Native queries aren’t supported by this value.”
This is almost certainly not the behavior you want. If you didn’t want to support native queries, you wouldn’t have implemented OnNativeQuery in the first place. Instead, you are supporting native queries, so most likely what you want is for the error your handler raised to be communicated out to the user. How do make this happen?
Wrap your error definition record in a call to Table.ViewError before raising the error. This will internally tag the error record so that Power Query propagates it out of Value.NativeQuery instead of having that method fallback to the default of dying with the complaint “native queries aren’t supported”.
For example:
OnNativeQuery = (query as text, optional parameters as any, optional options as nullable record) as any =>
if query = "Return Secret Code" then 123
else error Table.ViewError([Reason="Invalid Query", Message="Query's syntax is invalid"])
(Note: When you define the error, keep in mind that Table.ViewError requires the error to contain, at minimum, field Reason.)
[Related: Custom Connectors: Introducing Table.ViewError & Terminal Errors]
Further Query Folding
Handler OnNativeQuery should return whatever is appropriate, given the provided native query. This could be a scalar value, a table, etc. Whatever is appropriate can even be another table view! If a view is returned, additional query folding can take place on that view.
Below, if the OnNativeQuery handler used by Value.NativeQuery returns a table view, and that view implements handler GetRowCount, then Table.RowCount should fold to that handler.
Table.RowCount(Value.NativeQuery(Source, "abc"))
Nice—folding on top of a native query’s results. Powerful!
Support Status
The world of custom connectors is scantly documented. I’m not sure of the official Microsoft status of this handler. I do know that it is used by connectors that ship with Microsoft Power BI Desktop.
Based on my testing, at the technical level, OnNativeQuery works for custom connectors in both Power BI Desktop and in the Power BI Service (the latter, of course, requires an on-premise gateway).
Currently, Microsoft has a roadmap item that relates to this general area of functionality: Query folding on native queries – Power Query | Microsoft Doc I am curious to see how what that entails ties to what this article describes.