Forum Discussion

hpm's avatar
hpm
New Member
3 years ago
Solved

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

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_DATESURGEONRoomAssigned Hours
7/17/2023SmithOR 19.5
7/17/2023JacksonOR 27.5
7/17/2023JennieOR 22
7/17/2023LacyOR 39.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_DATESURGEONRoom
7/17/2023SmithOR 1
7/17/2023Jackson OR 2
7/17/2023JennieOR 2
7/17/2023LacyOR 3
7/18/2023GreenOR 1
7/18/2023PaulOR 3
7/19/2023LacyOR 4
7/19/2023JacksonOR 4
7/20/2023Ollie

OR 6

Here are a few columns from Table 2 titled Blocks

SURGERY_DATERoomSERVICEAssigned Block Hours
7/17/2023OR 1Head and Neck9.5
7/17/2023OR 2Gynecology7.5
7/17/2023OR 2Orthopedics2
7/17/2023OR 3Plastics9.5
7/18/2023OR 1Urology7.5
7/18/2023OR 3Head and Neck6.5
7/19/2023OR 4Orthopedics4
7/19/2023OR 4General Surgery5
7/20/2023OR 5Ophthalmology

8.5

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

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

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