Calculated Column with indirect relationship RRS feed

  • 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?

    Much thanks in advance!

    Table1 Table 2 Table3
    AssemblyID AssemblyID OrderID
    AssemblyDate OrderID OrderDate


    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