power query根据两个日期之间的交易日期合并两个表 [英] power query merge two tables based on the transaction date between two dates

问题描述

我正在尝试使用 Power Query 在两个表(1 - 交易表和 2 - 员工 ID 和日期范围)之间执行连接，其中交易日期介于两个日期之间.

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.

交易表

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

员工 ID

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

期望输出

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

如果我要在 SQL 中执行此操作，我会编写以下代码:

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

是否可以在 Excel Power Query 中执行此操作?谢谢.

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

推荐答案

进行标准合并，然后过滤.

Do a standard merge and then filter.

1. 使用内部联接合并查询.
2. 展开开始和结束日期列.
3. 选择满足条件的列.
4. 删除多余的列.

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"

这篇关于power query根据两个日期之间的交易日期合并两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！