Lookup Column in related table with a specific value in another column

[jaredj34]

I am a Power Query newbie and am trying to replicate a VLOOKUP in a one to many relationship.  I have 2 realated tables, Request and AuditLog with RequestCode as the related column.  What I would like to do is add a custom column with the date the Product Child Assigned to the Requests Table.  Is there a simple way to perform this Lookup?  Below is the sample data and some the code I have tried.  Thank you in advance.

Request Table

RequestCode                                                                 RequestName                 Date Assigned                       

OE45497.45499

Part Creation

Lookup Value

Audit Log Table

ActionDate              Status                             Action                                                          RequestCode

3/4/2021 4:22	New	Product Child Created	OE45497.45499
3/4/2021 4:22	ChildExecution	Product Child Assigned	OE45497.45499
3/4/2021 4:22	RequestInProgress	Product Child Submitted	OE45497.45499
3/4/2021 4:23	QCInprogress	Product Child Assigned for QC	OE45497.45499
3/4/2021 4:23	Ready for QC	Product Child Executed	OE45497.45499
3/5/2021 18:39	QC Complete	QC Pass	OE45497.45499
3/5/2021 18:39	QC Complete	Risk-Issue Confirmation	OE45497.45499
6/28/2021 20:57	ChildClose	Closed Product Child	OE45497.45499

 #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"RequestCode"}, GetAllChildRequestsAudit, {"RequestCode"}, "GetAllChildRequestsAudit", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Date Assigned", each  Table.SelectRows(GetAllChildRequestsAudit[ActionDate]),(GetAllChildRequestsAudit[Action] = "Product Child Assigned"))
in
    #"Added Custom"

[watkinnc]

I tried to be too fancy!

You can write it like:

#"Merged Queries" = Table.Join(#"Sorted Rows", {"RequestCode"}, Table.SelectRows(GetAllChildRequestsAudit, each [Action] = "GetAllChildRequestsAudit"),{"RequestCode"}, JoinKind.Inner)

--Nate

[jaredj34]

Thanks again @watkinnc!  I was able to get your suggestion to almost work.  With the Inner Join, just one row showed up on the table but I was expecting 140 from my sample data.  I switched the Join to a Left Outer and got the 140 rows I expected but the ActionDate only populated one of the rows.  

[watkinnc]

You can write it like:

#"Merged Queries" = Table.Join(#"Sorted Rows", {"RequestCode"}, GetAllChildRequestsAudit{[Action = "Product Child Assigned"]}, {"RequestCode"}, "GetAllChildRequestsAudit", JoinKind.Inner)

--Nate

[jaredj34]

@watkinnc  - Thanks for the response!!  Your code suggestion makes sense but I received an Expression Error.