Excel 2016 Power Query自定义函数：判断客户新购/升级后是否销户

核心逻辑梳理 #

• 按客户ID分组，提取每个客户的所有订单记录
• 对每个客户，筛选出Order Type为New或Upgrade且Start Date不为空的记录，取其中Start Date最晚的一条
• 筛选该客户的Disconnect订单，判断其Last Update Date是否晚于上述最晚的Start Date
• 满足条件则计算天数差，否则返回null；无有效New/Upgrade记录或仅Disconnect订单时直接返回null

示例数据 #

自定义函数实现 #

打开Power Query编辑器，进入「高级编辑器」，替换现有代码（假设数据源表名为Orders）：

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    // 统一转换日期格式，处理无效日期
    ConvertDates = Table.TransformColumns(Source, {
        {"Start Date", each try Date.From(_) otherwise null},
        {"Last Update Date", each try Date.From(_) otherwise null}
    }),
    // 按客户分组，提取每个客户最晚的有效New/Upgrade起始日期
    GroupByCustomer = Table.Group(ConvertDates, {"Customer ID"}, {
        {"AllRecords", each _, type table [Customer ID=any, Order Type=text, Start Date=date, Last Update Date=date]},
        {"LatestValidStart", each 
            let
                Filtered = Table.SelectRows(_, each ([Order Type] = "New" or [Order Type] = "Upgrade") and [Start Date] <> null),
                Sorted = Table.Sort(Filtered, {{"Start Date", Order.Descending}}),
                LatestStart = if Table.RowCount(Sorted) > 0 then Sorted{0}[Start Date] else null
            in LatestStart, type nullable date}
    }),
    // 展开记录并计算Disconnect After字段
    ExpandRecords = Table.ExpandTableColumn(GroupByCustomer, "AllRecords", {"Order Type", "Start Date", "Last Update Date"}, {"Order Type", "Start Date", "Last Update Date"}),
    CalculateDisconnectAfter = Table.AddColumn(ExpandRecords, "Disconnect After", each 
        let
            IsDisconnect = [Order Type] = "Disconnect",
            HasValidStart = [LatestValidStart] <> null,
            IsDateValid = [Last Update Date] <> null and [Last Update Date] > [LatestValidStart]
        in
            if IsDisconnect and HasValidStart and IsDateValid 
            then Duration.Days([Last Update Date] - [LatestValidStart])
            else null, type nullable number),
    // 移除辅助列
    RemoveHelperColumn = Table.RemoveColumns(CalculateDisconnectAfter, {"LatestValidStart"})
in
    RemoveHelperColumn

报错问题解决 #

1. 日期格式统一：原数据中日期可能为文本格式，先通过try Date.From(_) otherwise null转换为日期类型，避免运算报错
2. 空值前置判断：先检查Start Date和Last Update Date是否为空，再执行计算，避免空值运算错误
3. 分组逻辑优化：通过分组提取每个客户的有效New/Upgrade最晚日期，避免逐行判断的逻辑混乱

期望输出 #

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