Table.View - Handling NestedJoin + Expand

[Ben-Dev]

Hello,

I'm trying to build a Table.View that handles a Table.NestedJoin + Table.ExpandTableColumn sequence of operations.

let
  // returns a table that includes column "RelationshipColumn" which is present/output because, inside the view, Table.NestedJoin is called
  Base = GetSomeView(), 

  Expand = Table.ExpandTableColumn(Base, "RelationshipColumn", {"ID"})
in
  Expand

However, I'm having trouble figuring out what handlers the table view needs to implement to fully fold this. 🙂

For a non-nested join (i.e. Table.Join), then the view simply needs to implement OnJoin and the join folds. For a nested join, I thought maybe hanlders OnJoin or OnSelectColumns + OnJoin would be sufficient to handle things, but that doesn't appear to be the case.

Any help you can share would be most appreciated. 

Thank you!

[artemus]

Here is a list of handlers:

OnAddColumns = (constructors as {[Name = text, Function = (row as record) => any, Type = type]}) as table => ...

OnCombine = (tables as {table}, index as Int64.Type) as table => ...,

OnDistinct = (columns as {text}) as table => ...,

OnGroup = (keys as {text}, aggregates as {[Name = text, Function = (subtable as table) => any, Type = type]}) => ...,

OnInvoke = (function as function, arguments as list, index as Int64.Type) as any => ...,

OnJoin = (joinSide as Int64.Type, leftTable as table, rightTable as table, joinKeys as {text}, joinKind as JoinKind.Type) as table => ...,

OnPivot = (pivotValues as list, attributeColumn as {text}, valueColumn as text, aggregateFunction as (items as list) as any) as table => ...,

OnRenameColumns = (renames as {text}) as table => ...,

OnSelectColumns = (columns as {text}) => ...,

OnSelectRows = (condition as (row as record) as logical) as table => ...,

OnSkip = (count as Int64.Type) as table => ...,

OnSort = (order as {[Name = as text, Order as Order.Type]}) as table => ...,

OnTake = (count as Int64.Type) as table => ...,

OnUnpivot = (pivotColumns as {text}, attributeColumn as text, valueColumn as text) as table => ...,

OnNativeQuery = (query as text, optional parameters as any, optional options as any) as any => ...,

Some operations are not foldable, others still are expressed in terms of other operations like Table.NestedInnerJoin.

[Ben-Dev]

Hi @artemus,

Thank you for this list of handlers.

Could you tell me which handler (or sequence of handlers) a Table.NestedJoin call followed by a Table.ExpandTableColumn maps to?

Thank you!

[artemus · Accepted solution]

This maps to:

1. Table RenameColumns (if join columns have the same name)
2. Table.Join
3. Table.SelectColumns (if join columns have the same name)

Note: A nested join using left outer will not result in any calls to the connector. It simply adds a virtual table column waiting to be expanded. Other join types will break folding, performing an expand column afterwards will unbreak folding.

[Ben-Dev]

Thank you, @artemus!

I think I had trouble with this initially because the Table.NestedJoin was adding an OnSort call into the mix, which I wasn't handling.

[lbendlin]

May I interest you in this blog post

Chris Webb's BI Blog: Speed Up Data Refresh Performance In Power BI Desktop Using Table.View Chris W...

Also, you may want to consider this excellent primer on M plumbing.

Power Query M Primer (Part 12): Tables—Table Think I | Ben Gribaudo

[Ben-Dev]

Thanks, @lbendlin. I'm familiar with the general concept of Table.Views; but I'm not sure which handlers Table.NestedJoin + Table.ExpandTableColumns maps to.