如何通过PowerQuery基于同客户同文档历史日期计算到期日

问题背景 #

我拥有一份包含不同客户的数据集，这些客户的各类文档有不同的到期间隔。希望使用PowerQuery，根据同一客户同类型文档的历史完成日期，计算该文档的下一次到期日。

原始表名为Raw_Data_Table，包含三列：客户姓名、文档类型、完成日期，具体数据如下：

需求说明 #

需要基于同类型文档的历史完成日期确定当前文档的到期日，例如Client 1在01/01/24完成的Employment文档，其到期日应为12/01/23，对比后可知该文档逾期完成。

之前用Excel公式在D2单元格实现过，但存在缺陷：表格排序改变时易出错。公式如下：

=IF(AND(A2=A3,B2=B3,B2="Employment Assessment"),EOMONTH(C3+180,-1)+1,IF(AND(A2=A3,B2=B3,B2="Functional Assessment"),EOMONTH(C3+180,-1)+1,IF(AND(A2=A3,B2=B3,B2="Nicotine Assessment"),EOMONTH(C3+365,-1)+1,"")))

希望通过PowerQuery创建Due Date列，自动查找同一客户同类型文档的历史完成日期并计算到期日。

PowerQuery实现步骤 #

1. 导入数据到PowerQuery：加载Raw_Data_Table到PowerQuery编辑器。
2. 转换日期类型：将Done Date列转换为标准日期格式，避免计算错误。
3. 分组排序：按Name和Record Type分组，对每组内的Done Date按降序排序（最新日期在前）。
4. 添加组内索引：在分组后的每组内添加索引列，标记每条记录的顺序。
5. 匹配历史完成日期：为每条记录匹配同组内下一条（更早的）历史完成日期。
6. 计算到期日：根据文档类型的间隔规则计算到期日，无历史记录的条目留空。

PowerQuery M代码示例 #

let
    Source = Excel.CurrentWorkbook(){[Name="Raw_Data_Table"]}[Content],
    // 转换日期列为标准日期类型
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Done Date", type date}}),
    // 按客户+文档类型分组，每组内按完成日期降序排序
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Record Type"}, {{"Grouped", each Table.Sort(_,{{"Done Date", Order.Descending}}), type table}}),
    // 展开分组后的表格
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Grouped Rows", "Grouped", {"Done Date"}, {"Done Date"}),
    // 添加组内索引列
    #"Added Index" = Table.AddIndexColumn(#"Expanded Grouped", "Group Index", 0, 1, Int64.Type),
    // 匹配同组内的下一条历史完成日期
    #"Added Next Done Date" = Table.AddColumn(#"Added Index", "Next Done Date", (current) => 
        let
            nextRecord = Table.SelectRows(#"Added Index", 
                each [Name] = current[Name] 
                and [Record Type] = current[Record Type] 
                and [Group Index] = current[Group Index] + 1)
        in
            if Table.RowCount(nextRecord) > 0 then nextRecord{0}[Done Date] else null),
    // 计算到期日
    #"Added Due Date" = Table.AddColumn(#"Added Next Done Date", "Due Date", (current) => 
        if current[Next Done Date] <> null then
            let
                // 根据文档类型取间隔天数
                intervalDays = if current[Record Type] = "Nicotine" then 365 else 180,
                tempDate = Date.AddDays(current[Next Done Date], intervalDays),
                // 实现EOMONTH(tempDate,-1)+1的逻辑
                eomDate = Date.EndOfMonth(Date.AddMonths(tempDate, -1)),
                finalDueDate = Date.AddDays(eomDate, 1)
            in
                finalDueDate
        else
            null, type date),
    // 移除中间辅助列
    #"Removed Helper Columns" = Table.RemoveColumns(#"Added Due Date",{"Group Index", "Next Done Date"})
in
    #"Removed Helper Columns"

效果说明 #

• 代码不依赖原始表格的排序顺序，无论数据如何排列，都能准确匹配同一客户同类型的历史记录。
• 到期日计算逻辑与原Excel公式完全一致：Employment和Functional类型用180天间隔，Nicotine用365天间隔，最终生成EOMONTH(历史日期+间隔,-1)+1格式的到期日。
• 每组内最早的记录（无更早历史数据），Due Date列会显示为空。

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