You are working happily away on a Power Query custom data connector (or maybe on a standalone Table.View). Implement OnTake and OnSkip handlers? Check. Implement OnSelectColumns? Check. And on your journey goes, adding functionality by coding up new handlers—that is, until you realize you want to handle folding for a function where there doesn’t seem to be a corresponding handler.
Maybe it’s a standard library function like Table.ApproximateRowCount. Your data source provides a shortcut way to fetch a close-to accurate count and you’d like users of your custom connector to be able to retrieve that count using the standard function that already exists for this purpose. The catch? There’s no publicly documented GetApproximateRowCount handler which you can handle.
Instead, maybe it’s a custom function. Perhaps your data source maintains snapshots of historic data. Your connector users would like to be able to fetch data as it existed at a user-selected point in time by doing something like MySource.AsOf(someTableFromMySource, someDateTime). For this to work efficiently, that method needs to be foldable, but there’s no OnMySourceAsOf handler provided by Microsoft. Are you stuck or is there a way to fold custom methods?
In either case, the challenge is that you want to fold something that doesn’t have a specific handler.
The solution?
OnInvoke To the Rescue
If you’ve worked with Table.View for any length of time, you’re undoubtedly familiar with Get* and On* handlers that handle specific types of operations. For example, OnTake signals the view to limit how many rows it returns, while GetRowCount is for providing the count of rows in the table.
OnInvoke is the opposite: it’s generic vs. specific. It exists to enable the handling of foldable functions that do not map to a specific handler.
When OnInvoke is triggered, its first argument is the function that Power Query is asking your Table.View to consider folding. Your OnInvoke handler should test this argument to determine whether it wants to fold the indicated function. If yes, it should return an appropriate response; if not, it should raise an error.
Say you want to handle Table.ApproximateRowCount, like the introductory example proposes. Something like this makes that possible:
Table.View(
null,
[
…,
OnInvoke = (func, params, index) =>
if func = Table.ApproximateRowCount
then FetchApproxCountFromDataSource() // returns the "about right" row count
else … // raises an error to indicate that this handler isn't handling other functions
]
)
Notice that OnInvoke‘s handling of Table.ApproximateRowCount results in actual data being returned (the approximate row count). This behavior may not be what you’re used to when working with On* handlers. Normally, On* handlers return new Table.Views, not actual data. OnInvoke is an exception to this norm. What it should return is determined by context: if a new view is appropriate, it should return one; if actual data is appropriate (as it is in this case), it should return actual data.
Can OnInvoke be used with any standard library table function? No. Microsoft controls the list of library functions that OnInvoke can handle. As of this writing, to my knowledge, this list is not publicly documented, so experimentation is required to determine whether a particular library method can be folded using OnInvoke.
Folding Your Functions
But what about your functions—what if you want folding support for a custom function of your creation?
Meet Table.ViewFunction. This method takes a function as input, and returns a new function that’s the same as its input, except it is now registered as eligible for OnInvoke handling.
To implement our point-in-time example, we’d do something like:
shared MyData.Contents = () =>
let
GetView = (state as record) =>
Table.View(
null,
[
GetType = () => ...,
GetRows = () => ...,
OnInvoke = (func, args, index) =>
if func = MyData.AsOf
then
let
asOf =
if List.Count(args) <> 2
then error "Unexpected number of arguments"
else Value.As(args{1}, type datetime)
in
@GetView(state & [AsOf = asOf])
else ...
]
)
in
GetView([]);
MyData.InternalAsOf = (data as table, referencePoint as datetime) => ... /* non-folding implementation goes here */;
shared MyData.AsOf = Table.ViewFunction(MyData.InternalAsOf);
Unlike the previous example, which showed OnInvoke handling Table.ApproximateRowCount by returning actual data, this example follows the typical On* handler pattern of returning a new view that internally captures the requisite state (in this case, the as-of timestamp). In this situation, this is appropriate. Here, the need is for MyData.AsOf to configure the view’s behavior (which will influence how the view’s GetRows handler later behaves when it is called), not for it to directly output data.
Also of note: Since the actual MyData.AsOf function isn’t invoked when this example runs, that method’s argument type assertions and expected argument count are not enforced by the mashup engine. When OnInvoke is used, the handler assumes responsibility for performing any desired validation as to argument count and type compatibility. This is the purpose of the List.Count check and Value.As test, above.
For your testing convenience, as well as to illustrate that Table.ViewFunction isn’t limited to section members, here’s the preceding example rewritten as a self-contained let expression instead of as a section document:
let
GetView = (state as record) =>
Table.View(
null,
[
GetType = () => ...,
GetRows = () => ...,
OnInvoke = (func, args, index) =>
if func = MyData.AsOf
then
let
asOf =
if List.Count(args) <> 2
then error "Unexpected number of arguments"
else Value.As(args{1}, type datetime)
in
@GetView(state & [AsOf = asOf])
else ...
]
),
MyData.InternalAsOf = (data as table, referencePoint as datetime) => ... /* non-folding implementation goes here */,
MyData.AsOf = Table.ViewFunction(MyData.InternalAsOf),
Data = GetView([])
in
MyData.AsOf(Data, #datetime(2022, 01, 01, 12, 00, 00))
No Guarantees
Important: There’s no guarantee that a view function (whether a custom function, which you register using Table.ViewFunction, or one from the standard library, which Microsoft has already registered) will always be a candidate for folding. For example, above, imagine that a call to Table.Buffer precedes the call to MyData.AsOf. Since buffering blocks subsequent folding, it’s guaranteed that the call to MyData.AsOf won’t be folded.
When a view function isn’t folded, the view’s OnInvoke handler won’t be called for it (obviously!). Instead, the method will be evaluated like OnInvoke doesn’t exist. That is, its normal method body will be evaluated locally by the mashup engine.
If you define a publicly consumable view function, be sure it has a method body that works; don’t assume that it will always be skipped and instead have its behavior handled by the code in OnInvoke!
Argument Reference
OnInvoke = (func, arguments, index) => ...
OnInvoke is passed three arguments, in the following order:
- The function which triggered the handler—a.k.a. the function that is candidate for folding. In the first example, this is
Table.ApproximateRowCount. - A list of arguments passed to the candidate for folding function. In the
MyData.AsOfexample, this list contains the two arguments passed to that method. - A 0-based index identifying the item in the argument list that corresponds to the current view. For example, when OnInvoke is called for
MyData.AsOf, index = 0, signifying thatMyData.AsOf‘s first argument is the current view.
It’s a Wrap—No, It’s a Join (wait—maybe that’s next time!)
Based on the above, it’s easy to see how OnInvoke is interesting and can be quite useful. What may not be obvious is that it is an essential part in making the query folding of joins practical. Sounds like a good topic for another post….
Thanks to Curt Hagenlocher (of the Power Query team) for his response to several Microsoft Data Connector GitHub questions which shed light on how this mostly undocumented aspect of query folding works.