Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)
=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)