Forum Discussion

georgec96's avatar
georgec96
Helper II
4 years ago
Solved

one to many relationship RELATED () function

Hi everyone,

 

I have two tables with and trying to get the related column from the many side of the relationship to the one side, however the function RELATED() does not work.

 

I have a  table called open_orders and one table called open_lines which contains duplicate values, I would like to get the status column from the open_lines and bring it to the open_orders table.

 

 

 

 

Could anyone explain me how could i achieve that?

 

Thank you.

  • johnt75's avatar
    johnt75
    4 years ago

    You would need a column containing unique identifiers in case of multiple entries on the same date. If there isn't such a column in the data already, you could add an index column in Power Query and use that.

    Then you can create a column like

    Status =
SELECTCOLUMNS (
    TOPN (
        1,
        RELATEDTABLE ( order_lines ),
        order_lines[status date], DESC,
        order_lines[unique ID], DESC
    ),
    "@val", order_lines[Status]
)

6 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    RELATED works from the many side to the one side, you need RELATEDTABLE. You could create a column like

    Status = MINX( RELATEDTABLE(open_lines), open_lines[status])
    • georgec96's avatar
      georgec96
      Helper II
      4 years ago

      johnt75 

       

      Thank you, that worked perfectly. Would you be able to explain how does it work though? If there is duplicate values which one is it retrieving?

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        It will get the first one in alphabetical order, that is what the MIN is doing. From the screenshots it looked like all entries for the same order had the same status, if that's not the case then you could look to combine SELECTCOLUMNS, TOPN and RELATEDTABLE to sort the related entries by whichever columns were relevant