Finding most recent values between two tables

[Anonymous]

I've tried a couple of M formulas from older posts but I'm in the weeds with this one as an M Language novice.

I have two tables. Both can be linked via the unique identifier "machine number". Periodically they need a part replaced, which is shown in the "Part Number" column, in the Part Numbers table when the new item has gone in:

In the other table is a record of customer complaints, which shows when a complaint has been completed by Machine Number (Complaints Table):

I need to find a way to link the two together that tells me, for a given Complaint Completed Date (Complaints Table) what Part Number (Part Numbers Table) the Machine Number had.

For example: the Complaint Completed on 02/01/2021 should return a value of Part Number "1", whereas the Complaint Completed on 22/01/2021 should return me a Part Number "2" and so on. Any guidance appreciated! 🙂 

[jennratten · Accepted solution]

Hello - this will do the trick... You will need to add the name of your previous step into the first line where indicated.  Also, if your tables are named something other than "Parts Table" and "Complaints Table" then you will need to update those names in the script also.  Please let me know if you have any questions.

Table.AddColumn(#"Your Previous Step Name Goes Here", "Custom", each List.Max ( Table.SelectRows(#"Parts Table", (x)=> x[Machine number]=[Machine number] and x[Release Date] < [Complaint Completed Date])[Part Number] ), Int64.Type)