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

Question

I am a Power Query newbie and am trying to replicate a VLOOKUP in a one to many relationship.&nbsp; I have 2 realated tables, Request and AuditLog with RequestCode as the related column.&nbsp; What I would like to do is add a custom column with the date the Product Child Assigned to the Requests Table.&nbsp; Is there a simple way to perform this Lookup?&nbsp; Below is the sample data and some the code I have tried.&nbsp; Thank you in advance.&nbsp;Request TableRequestCode&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RequestName&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date Assigned&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;OE45497.45499Part CreationLookup Value&nbsp;Audit Log TableActionDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Action&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RequestCode3/4/2021 4:22NewProduct Child CreatedOE45497.454993/4/2021 4:22ChildExecutionProduct Child AssignedOE45497.454993/4/2021 4:22RequestInProgressProduct Child SubmittedOE45497.454993/4/2021 4:23QCInprogressProduct Child Assigned for QCOE45497.454993/4/2021 4:23Ready for QCProduct Child ExecutedOE45497.454993/5/2021 18:39QC CompleteQC PassOE45497.454993/5/2021 18:39QC CompleteRisk-Issue ConfirmationOE45497.454996/28/2021 20:57ChildCloseClosed Product ChildOE45497.45499&nbsp;&nbsp; #"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"&nbsp;&nbsp;

anonymous · Answer

You can write it like:#"Merged Queries" = Table.Join(#"Sorted Rows",&nbsp;{"RequestCode"},&nbsp;GetAllChildRequestsAudit{[Action = "Product Child Assigned"]},&nbsp;{"RequestCode"},&nbsp;"GetAllChildRequestsAudit", JoinKind.Inner)&nbsp;--Nate

anonymous · Answer

Anonymous&nbsp; - Thanks for the response!!&nbsp; Your code suggestion makes sense but I received an Expression Error.&nbsp;&nbsp;

anonymous · Answer

I tried to be too fancy!&nbsp;You can write it like:#"Merged Queries" = Table.Join(#"Sorted Rows",&nbsp;{"RequestCode"},&nbsp;Table.SelectRows(GetAllChildRequestsAudit, each [Action] = "GetAllChildRequestsAudit"),{"RequestCode"},&nbsp;JoinKind.Inner)&nbsp;--Nate

anonymous · Answer

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

Forum Discussion

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

4 Replies

Related content

Filtercontext with date relation

Specific matrix

Unable to Relate to another Table

Relation

Dataflows Gen2 Error : network-related or instance-specific error

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