Forum Discussion
Table.View - Handling NestedJoin + Expand
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!
This maps to:
- Table RenameColumns (if join columns have the same name)
- Table.Join
- 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.
6 Replies
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.
This maps to:
- Table RenameColumns (if join columns have the same name)
- Table.Join
- 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.
May I interest you in this blog post
Also, you may want to consider this excellent primer on M plumbing.
Power Query M Primer (Part 12): Tables—Table Think I | Ben Gribaudo
