Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ben-Dev
Helper II
Helper II

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!

1 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.

View solution in original post

6 REPLIES 6
artemus
Employee
Employee

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.

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!

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.

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
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors