# Calculated Column with indirect relationship

• ### Question

• Hi,

I want to make a calculated column in Table 1 in Power Query.
The calculated value needs to be the OrderDate from table 3.

OrderID are unique values as wel as AssemblyID.

Relationships are:
Table1 and Table2: AssemblyID

Table2 and Table3: OrderID

How can I get this calculated value?

 Table1 Table 2 Table3 AssemblyID AssemblyID OrderID AssemblyDate OrderID OrderDate

Greetings,
Cas

Friday, October 18, 2019 9:10 AM

• Hello

in reference to the file provided by Herbert, add a new column to Table1C and add this function. This will do the trick. This works only if unique values are provided in Table2C and Table3C

`try Table3C[OrdDt]{List.PositionOf(Table3C[OrdID],Table2C[OrdID]{List.PositionOf(Table2C[AssyID],[AssyID])})} otherwise ""`

Saturday, October 19, 2019 9:05 AM

### All replies

• Hi,

Calculated column in Table1:

`=RELATED(Table3[OrderDate])`
Friday, October 18, 2019 11:34 AM
• Hello

You can't really create relationships in power query.

You have to either do a nestedjoin of tables, and then expand the desired column or create a function that does a INDEX(MATCH), meaning using List.PositionOf in combination of Table-Column-reference.

Jim

Friday, October 18, 2019 12:01 PM
• Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Generated random, non-sense data for Tables 1 to 3.
Since some functions can be done in either PP or PQ, randomly picked either.
Could not see any data that lends to aggregation.
http://www.mediafire.com/file/vrtr9os87trntkg/10_18_19d.xlsx/file
http://www.mediafire.com/file/n2wkchya3446s6b/10_18_19d.pdf/file

Saturday, October 19, 2019 2:54 AM
• Hello

in reference to the file provided by Herbert, add a new column to Table1C and add this function. This will do the trick. This works only if unique values are provided in Table2C and Table3C

`try Table3C[OrdDt]{List.PositionOf(Table3C[OrdID],Table2C[OrdID]{List.PositionOf(Table2C[AssyID],[AssyID])})} otherwise ""`

Saturday, October 19, 2019 9:05 AM
• Added Jimmy's neat trick to my file.
Saturday, October 19, 2019 4:43 PM