powerquery - power query merge two tables based on the transaction date between two dates

Question

I'm trying to perform a join between two tables (1 - transaction table and 2 - employee ID and date range) using Power Query where the transaction date is between two dates.

Transaction Table

+-------+-----------------+--------+
| EmpID | TransactionDate | Amount |
+-------+-----------------+--------+
|   123 | 5/5/2019        |     30 |
|   345 | 2/23/2019       |     40 |
|   456 | 4/3/2018        |     50 |
+-------+-----------------+--------+

Employee ID

+-------+-----------+-----------+
| EmpID | StartDate |  EndDate  |
+-------+-----------+-----------+
|   123 | 5/1/2019  | 5/30/2019 |
+-------+-----------+-----------+

Desired Output

+-------+-----------------+--------+
| EmpID | TransactionDate | Amount |
+-------+-----------------+--------+
|   123 | 5/5/2019        |     30 |
|   456 | 4/3/2018        |     50 |
+-------+-----------------+--------+

If i were to do this in SQL, i would write the following code:

select *
from transaction as A
inner join empID_date as B
on A.EmployeeID = B.EmployeeID
and A.TransactionDate >= B.StartDate
and A.TransactionDate <= B.EndDate

is it possible to do this in Excel Power Query? thanks.

Answer 1

Do a standard merge and then filter.

1. Merge the queries with an inner join.
2. Expand the start and end date columns.
3. Select columns satisfying your conditions.
4. Remove extra columns.

let
    Source = < Transaction table source or definition goes here >,
    #"Merged Queries" = Table.NestedJoin(Source, {"EmpID"}, emp_ID, {"EmpID"}, "emp_ID", JoinKind.Inner),
    #"Expanded emp_ID" = Table.ExpandTableColumn(#"Merged Queries", "emp_ID", {"StartDate", "EndDate"}, {"StartDate", "EndDate"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded emp_ID", each [TransactionDate] >= [StartDate] and [TransactionDate] <= [EndDate]),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"StartDate", "EndDate"})
in
    #"Removed Columns"