Forum Discussion
Helper IIone to many relationship RELATED () function
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] )
Super UserRELATED 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])
georgec96Thank 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
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