如何在Power BI Query中复刻MS Access查询的DLookup功能？

我有两张需导入Power BI进行分析的表：

Table1（转科记录表） #

记录患者转至病房的信息，每次转科生成一条包含admissionID、Ward、Date-Time的记录：

Table2（操作记录表） #

记录用户针对admissionID执行的操作信息，包含admitID、User、Field-Changed、Change-Date-Time：

举个例子：BobSmith在05-01-2000执行操作时，患者实际在Ward 1，因为转至Ward 2的时间晚于操作时间，不能用这条记录。

在MS Access里，我用以下表达式获取对应时间的Ward：

Ward: DLast("[Ward]","Table1","[admissionID]=" & [admitID] & " AND [Date-Time]<#" & Format([Change-Date-Time],"mm/dd/yyyy hh:nn:ss AM/PM") & "#")

请问怎么在Power BI里复刻这个查询的输出？我试过追加查询加时间对比列，但因为同个患者有多个前置转科记录，这个方法行不通。

方法一：用DAX创建计算列 #

直接在Table2里新建计算列，通过筛选匹配时间区间的最新转科记录：

对应Ward = 
VAR 当前患者ID = Table2[admitID]
VAR 当前操作时间 = Table2[Change-Date-Time]
RETURN
CALCULATE(
    LASTNONBLANKVALUE(Table1[Date-Time], Table1[Ward]),
    Table1[admissionID] = 当前患者ID,
    Table1[Date-Time] <= 当前操作时间
)

逻辑解释： #

1. 先锁定当前行的患者ID和操作时间
2. 筛选Table1中同ID、且转科时间早于/等于操作时间的所有记录
3. 用LASTNONBLANKVALUE取这些记录里时间最晚的那一条对应的Ward值，也就是操作时患者所在的病房

如果你的转科时间没有重复，也可以用更简洁的写法：

对应Ward = 
VAR 当前患者ID = Table2[admitID]
VAR 当前操作时间 = Table2[Change-Date-Time]
RETURN
CALCULATE(
    MAX(Table1[Ward]),
    Table1[admissionID] = 当前患者ID,
    Table1[Date-Time] <= 当前操作时间
)

方法二：用Power Query预处理数据 #

如果想在加载数据前完成匹配，用Power Query步骤如下：

1. 打开Power Query编辑器，选中Table2
2. 点击「合并查询」，选择Table1，匹配条件设为admitID等于admissionID，连接类型选「左外部」
3. 展开合并后的Table1列，只保留Ward和Date-Time字段
4. 添加自定义列，公式写[Date-Time] <= [Change-Date-Time]，命名为「时间符合」
5. 按admitID和Change-Date-Time分组，分组操作选「所有行」，新列名设为「分组记录」
6. 再添加自定义列，提取分组里符合时间条件的最新转科记录的Ward：

   Table.Max(Table.SelectRows([分组记录], each [时间符合] = true), "Date-Time")[Ward]
   

7. 最后删掉不需要的中间列，整理结果即可

内容的提问来源于stack exchange，提问作者spudsta