Have a table, but want to change—to override or augment—some of its behaviors? Not a technique normally needed in day-to-day Power Query, but perhaps useful in certain special scenarios.
Imagine that you are pulling a table containing many product rows from a data source. All works well, except for one performance challenge: Counting rows (e.g. Table.RowCount) is slow because the data connector doesn’t query fold the row count. Instead, Power Query is fetching all the rows in the table in order to count them locally, and this takes a while.
You know that there’s an API endpoint, /product/count, that can be called which will immediately return the count of products. If only you could use the existing data connector, which you are otherwise happy with, and just override how row counting is handled for this table so that it uses /product/count….
Overriding an Event
Table.View allows just this. More generally, Table.View can be used in a couple of ways, including augmenting and overriding how query folding works for an existing table. This is done by using Table.View to register handler functions which Power Query can use when attempting query folding. Table.View returns a view over the existing table with the specified events overridden, which otherwise behaves just like the original table.
In our example, the handler of interest is GetRowCount. If we register a handler for this event using Table.View, then when Power Query encounters a Table.RowCount that could potentially fold, it will try calling our handler.
Let’s give this a try:
let
Products = // replace the hard-coded #table with whatever code is necessary to get the products table from the existing data source's connector
#table(type table [Product ID = Int64.Type, Category = text], {{1, "Widgets"}}),
Products_Wrapped = Table.View(
Products,
[
GetRowCount = () => 5
]
)
in
Table.RowCount(Products_Wrapped) // returns 5, regardless of how many rows are actually in the table
The above row count evaluates to 5, because that’s the hard-coded number our GetRowCount handler returns. This result demonstrates that query folding took place and that our handler is what produced the query folded response…but hard-coded row counts are not super useful. Instead, we should change the handler function to something that fetches the actual product count from the appropriate API endpoint…perhaps something along the lines of:
GetRowCount = () => Number.From(Text.FromBinary(Web.Contents(ApiBaseUrl, [RelativePath = "/product/count"])))
With this, we have the best of both worlds: A table (implemented as a table view) that works just like the original table from the existing data connector—except in the case of row counts over the entire table. For those counts, a behavior change has been made so that their result is produced more efficiently. Excellent!
Selecting Rows (and Parsing ASTs)
How about another scenario: Perhaps row counts aren’t the issue. Instead, the area of desired improvement is fetching products filtered to just one category.
Say the existing data source doesn’t know how to query fold code like Table.SelectRows(Products, each [Category] = "Widgets"). Without query folding, the preceding still works, but involves all product rows being read from source and filtered locally by Power Query. We know there’s an API endpoint that allows just the products for a given category to be fetched (e.g. /category/?Name=Widgets)…but is there a way to wire up a Table.View so that this endpoint is used when it is relevant?
Absolutely! To make this happen, the view needs to handle OnSelectRows, but just for those cases where the table is being filtered to a single product category.
The handler we register should be in the following form:
OnSelectRows = (predicate) => …
When invoked, predicate will be the function that was passed as the second argument to Table.SelectRows. (For example, in the case of Table.SelectRows(Products, each [Category] = "Widgets"), the predicate is the function each [Category] = "Widgets".)
In our handler, what should we do with the predicate function? We could invoke it, but that would result in being processed locally by Power Query—which is what we are trying to get away from, at least for simple category equals value filters. Instead, we need to determine what the predicate function would do if it were invoked. Specifically, we need to check whether it performs an equality comparison comparing field “Category” to a value, and if so, find out the value that it wants “Category” to equal.
To make this happen, we need to get an abstract syntax tree (AST) for the predicate function. Then, we can check that tree for the information we need.
RowExpression.From produces the needed AST from the function. In the case of each [Category] = "Widgets", what it returns is equivalent to the following:
[
Kind = "Binary",
Operator = "Equals",
Left = RowExpression.Column("Category"), // which equals: [Kind = "FieldAccess", MemberName = "Category", Expression = ItemExpression.Item]
Right = [Kind = "Constant", Value = "Widgets"]
]
Once we have the predicate function’s AST, we first need to check whether it matches the form we expect. If it does, we can read the expected category name from it, then use that category name to fetch the appropriate results. If it does not, we simply delegate handling the row selection event to the table that our view wraps by calling Table.SelectRows on that table, passing it the original filter predicate.
let
Products = // replace the hard-coded #table with whatever code is necessary to get the products table from the existing data source's connector
#table(type table [Product ID = Int64.Type, Category = text], {{1, "Widgets"}}),
Products_Wrapped = Table.View(
Products,
[
OnSelectRows = (predicate) =>
let
GetProductsForCategory = (category) => ..., // do something like the following, then turn its results into a table: Web.Contents(ApiBaseUrl, [RelativePath = "/product", Query= [Name = category]])
AST = RowExpression.From(predicate),
CategoryName =
if
AST[Kind] = "Binary"
and AST[Operator] = "Equals"
and AST[Left] = RowExpression.Column("Category")
and AST[Right][Kind] = "Constant"
then AST[Right][Value]
else Table.SelectRows(Products, predicate)
in
GetProductsForCategory(CategoryName)
]
)
in
Table.SelectRows(Products_Wrapped, each [Category] = "Widgets")
(If the above captures your curiosity, a small next step exploration would be to expand the handler to support not just each [Category] = "Widgets" but also each "Widgets" = [Category]. If you want to keep going, how about handling List.Contains({"Widgets", "Thingamajigs"}, [Category]), as well? To learn more about RowExpression.From‘s ASTs, the article RowExpression.From/ItemExpression.From I wrote a while back may be interesting.)
There we have it: Filtering to just one category is now more efficiently handled!
While each of our examples has handled only one event, this singularity isn’t a limitation of table views. Any number of handlers can be used with a single view by including them in the record passed as Table.View‘s second argument (so, for example, you could easily build a view that includes both GetRowCount and OnSelectRows from our previous examples).
Conclusion
Is using Table.View to augment (or override) an existing table’s behavior something you’ll do every day? Almost certainly not. But once in a rare while, doing so might come in handy—and even if you never use this technique, I hope exploring this rarely-touched area of Power Query was something you found interesting.
This discussion isn’t an exhaustive exploration of all that’s possible with Table.View, or of all potential nuances involved with implementing handlers on a view, but rather just a brief tour to whet your appetite about this little known realm of Power Query. I hope you enjoyed it!