Forum Discussion
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_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 |
=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)
1 Reply
- wdx223_DanielCommunity Champion
=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)