从客户编号提取起始日期遇解析错误，求排查与解决方法

一、先明确之前报错的核心原因 #

1. 解析逻辑错误：你之前用Date.From([Customer_Num])是把整个客户编号当作日期字符串解析，98%的成功率只是巧合——这些编号的前8位刚好是合法的YYYYMMDD格式（比如20230801xxxx），而报错的编号前8位并非有效日期（比如202405xx的前8位是202405xx，不符合日期格式），本质是逻辑方向错了，不是单纯的脏数据问题。
2. 提取前6位后解析失败的原因：Date.From无法直接识别YYYYMM格式的字符串（比如202405），它必须接收包含日的完整日期格式（比如20240501），所以直接解析前6位必然全失败。
3. 剩余23行报错的可能点：导出这些行检查前6位内容：
   • 是否存在非数字字符（比如隐藏的不可见符号、字母）
   • 月份是否超出1-12范围（比如202413xx）
   • 年份格式异常（比如002405xx）

二、全量解析的正确实现方法 #

核心逻辑是：提取前6位年月→拼接当月第一天（01）→指定格式解析为日期→加错误处理兼容异常数据。

自定义列代码（Power Query M） #

= let
    // 清理编号中的空格、不可见字符
    CleanedNum = Text.Clean(Text.Trim([Customer_Num])),
    // 提取前6位年月，长度不足6位则返回空
    YearMonth = if Text.Length(CleanedNum) >= 6 then Text.Start(CleanedNum, 6) else null,
    // 验证年月合法性：年份为数字、月份在1-12之间
    IsValid = YearMonth <> null 
        and Text.Length(YearMonth) = 6
        and try Number.From(Text.Start(YearMonth, 4)) is number otherwise false
        and try Number.From(Text.End(YearMonth, 2)) is number otherwise false
        and Number.From(Text.End(YearMonth, 2)) >= 1 and Number.From(Text.End(YearMonth, 2)) <= 12,
    // 拼接成YYYYMMDD格式的当月第一天
    FullDateStr = if IsValid then YearMonth & "01" else null,
    // 解析为日期并取当月起始日，异常则返回空
    StartDate = try Date.StartOfMonth(Date.FromText(FullDateStr, [Format="yyyyMMdd"])) otherwise null
in
    StartDate

代码关键说明 #

• Text.Clean+Text.Trim：彻底清除编号中的空格、换行、制表符等隐藏脏数据
• 合法性校验：避免解析202413（13月）、202400（0月）这类无效值
• try otherwise：捕获所有异常，确保不会因个别脏数据导致整列报错
• 指定Format解析：用Date.FromText加格式参数，比Date.From更稳定，不依赖系统区域设置

三、排查剩余异常行的技巧 #

如果仍有少量行返回空，按以下步骤排查：

1. 筛选出StartDate为空的行
2. 新增一列提取YearMonth的值，拆分每个字符检查：
   • 是否存在非数字字符（比如20240x里的字母x）
   • 月份是否在1-12区间内
3. 对确实无效的年月（比如202499），可根据业务需求返回空、标记为异常，或手动修正

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