How to return values from table2's column if two columns from table2 are equal to table1's columns

[hpm]

Hello, 

My task is to create a custom column labeled "Assigned Hours" in the Cases query that returns Blocks[Assigned Block Hours] when Cases[SURGERY_DATE] = Blocks[SURGERY_DATE] and Cases[Room] = Blocks[Room]

At first, I tried:

if [SURGERY_DATE] = Blocks[SURGERY_DATE] and [Room] = Blocks[Room] then Blocks[Assigned Block Hours] else null 

Then, I tried Table.ContainsAll and List.PositionOf but I've been having issues since I'm trying to look up two values rather than one.

Here is my desired outcome:

SURGERY_DATE	SURGEON	Room	Assigned Hours
7/17/2023	Smith	OR 1	9.5
7/17/2023	Jackson	OR 2	7.5
7/17/2023	Jennie	OR 2	2
7/17/2023	Lacy	OR 3	9.5

*This is just an example, not my actual data. Tables have different number of rows and columns. Any help would be greatly appreciated. Thank you.

Here are a few columns from Table 1 titled Cases

SURGERY_DATE	SURGEON	Room
7/17/2023	Smith	OR 1
7/17/2023	Jackson	OR 2
7/17/2023	Jennie	OR 2
7/17/2023	Lacy	OR 3
7/18/2023	Green	OR 1
7/18/2023	Paul	OR 3
7/19/2023	Lacy	OR 4
7/19/2023	Jackson	OR 4
7/20/2023	Ollie	OR 6

Here are a few columns from Table 2 titled Blocks

SURGERY_DATE	Room	SERVICE	Assigned Block Hours
7/17/2023	OR 1	Head and Neck	9.5
7/17/2023	OR 2	Gynecology	7.5
7/17/2023	OR 2	Orthopedics	2
7/17/2023	OR 3	Plastics	9.5
7/18/2023	OR 1	Urology	7.5
7/18/2023	OR 3	Head and Neck	6.5
7/19/2023	OR 4	Orthopedics	4
7/19/2023	OR 4	General Surgery	5
7/20/2023	OR 5	Ophthalmology	8.5

[wdx223_Daniel · Accepted solution]

=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)