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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
artemus
Microsoft Employee
Microsoft Employee

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
Microsoft Employee
Microsoft 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!

artemus
Microsoft Employee
Microsoft Employee

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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