How do I do an Outer Join using two critera, one of which is variable?

[amail]

Hello all.

I have the following two tables:

Employee Table EMP RATE EFFDATE Brandon $13.50 2/1/17 Sean $12.00 2/1/17 Brandon $14.50 4/1/17 Melanie $12.00 2/1/17 Melanie $13.00 3/1/17 Brandon $16.00 5/1/17

Job Table JOB DATE EMP HOURS 0001 2/15/17 Brandon 3 0001 2/15/17 Sean 3 0002 2/25/17 Sean 2 0002 2/25/27 Melanie 3 0003 3/10/17 Melanie 6 0003 3/10/17 Brandon 4 0004 4/15/17 Brandon 3 0005 5/1/17 Brandon 6 0005 5/1/17 Melanie 5

I want a column on the Job Table that uses the correct rate for the employee and the date range. If I do a full outer join on EMP and DATE, I get three entries for every job with Brandon and two entries for every job with Melanie. I want just the single entry with the correct rate for the date.

Color me stumped.

 

[amail]

Maybe it would be better to attach the spreadsheet so you can see what I'm up against. I mis-spoke in my first post. I'm not getting several values for each employee, just one, but it's still not what I need - Rate cannot be null, people need to be paid!
View attachment qryTest.xlsx

 

[MarcelBeug]

You can join on EMP only and then select the rate with the latest effdate <= job date, like in the following code:

let
    Source = Table.NestedJoin(qryJOBS,{"EMP"},qryEMP,{"EMP"},"NewColumn",JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Rate", (This) => Table.Max(Table.SelectRows(This[NewColumn], each _[EFFDATE] <= This[DATE]),"EFFDATE")[RATE]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"NewColumn"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Rate", Currency.Type}})
in
    #"Changed Type"

Beware: if you adjust the #"Added Custom" step via the gear button at the right of the step name, than you'll get "each " in front of "(This) =>" and you need to remove that "each '.

 

[amail]

Thanks so much - this has saved me a lot of grief! I guess I have to learn about (This).

 

[amail]

I'm curious how you built that custom column. Would you mind stepping me thru the steps?