Custom Column using data from two tables

[Ronnie_L]

I have a PowerBI/Power Query Editor formula question I can't solve. I have two tables with common column of Payroll ID. The first table has a column "from" that is populated with the date an employee either took leave or received overtime. Another column has the Payroll ID "employeePayrollID".

The second table has the common column "Payroll ID" as well as columns "From", "Through" and "Shift Mask". If an employee changes days off a new row is created with the appropriate dates.

I need a formula to look at the date of the "from" column in table 1 and get the "employeePayrollID" then go to table 2, find the "Payroll ID" and compare the date to the "From" and "Through" columns and tell me the employees days off, which is column "Shift Mask".

In my example posted below, the employee took leave or received overtime multiple times. The same employees shift or days off change five times. So I need to know the employees days off on the day of the leave or overtime, 9/6/2021, 9/10/2021, 9/11/2021... and the info is in "Shift Mask" from table 2.

Thanks,

Ronnie

[Ronnie_L]

I still need help. I don't think my explination was sufficient. I tried the merge but it didn't work. I understand merging by payroll ID, but I think the issue is merging with the "from" dates becasue they aren't uniform over all the employees. And I understand changing "from" in table 1 to date from date/time. When I tried it, it gave me the five options for the employees days off for each row. I only want the correct days off.

I need a new column, "DaysOff" in table 1 for days off, the "shift mask" column in table 2.  So I need the formula to look at "from" and payroll id in table 1, go to table 2 and find the payroll id, then find the correct days off based on the "from" & "through" columns in table 2.

Based on above, for row 1 in table 1, September 6, 2021 the new "DaysOff" column should read "Sunday Monday Tuesday Off". On later rows where the OT or leave was on March 17, 2023, for example, the new column should read "Saturday Sunday Off'. If it was on May 19, 2023 it should read "Sunday Monday Tuesday Off", ...

Hopefully this makes more sense. 

Ronnie

[jennratten]

Hello - to get table 2 records which match table 1 records on payroll Id and from date, you can transform the column type of the from date field in table 1 to date (currently date/time) and then you can merge the two tables on the payroll ID and from date, then expand the results to get the Shift Mask. If this is not what you are looking to return I am glad to continue to help; please just provide an example that shows the expected results.  In your snips provided, there are not any records that match on payroll ID and from date.

Thanks!