I currently have two tables - "On-Hold Table" and "Submission Lifecycle" Table in the Power Query Editor in PowerBI, with the important columns below.
On-Hold Table - id, on_hold_date, off_hold_date
Submission Lifecycle - id, submission_number, submission open date, submission end date
My goal is to add the submission_number from the Submission Lifecycle table to the On-Hold Table. The submission_number returned should be the latest open date (prior to the on_hold_date).
Example:
On Hold Table: id=HG123, on_hold_date=01/05/2022, off_hold_date=1/8/2022 - SHOULD bring back submission_number=2
Submission Lifecycle:
id=HG123, submission_number=1, submission open date=12/01/2021, submission end date=12/31/2021
id=HG123, submission_number=2, submission open date=01/04/2022, submission end date=null
Additional Notes:
- id can be duplicated in both tables (but the combination of the id & submission_number only appears once in the Submission Lifecycle table)
- the off_hold_date could be null or a date
- submission end date could be null or a date
- submission open date will not be null
- Time stamp is included with all dates (don't know if that matters)
I have done this in excel, but can't figure out how to do it in the Power Query
I hope someone can help, thank you for your time!
