将Excel时长转换公式迁移至Power Query并解决超24小时时长问题

1. 基础转换：Excel公式转M语言代码 #

在Power Query中添加自定义列，用M语言复刻原Excel公式的逻辑，代码如下：

= let
    ageText = [Age],
    len = Text.Length(ageText)
in
    if len = 7 then "0" & ageText
    else if len = 5 then "00:" & ageText
    else if len = 4 then "00:0" & ageText
    else if len = 3 then "00:00" & ageText
    else ageText

代码说明 #

• ageText = [Age]：提取当前行的Age列值存入变量，简化后续调用
• len = Text.Length(ageText)：计算字符串长度，对应Excel的LEN()函数
• 嵌套if...else逻辑完全匹配原公式，实现不同长度字符串的补全

2. 处理超过24:00:00的时长 #

若需将补全后的字符串转为可计算的时长类型（而非纯文本），可借助Power Query的Duration.FromText()函数，同时要注意Excel的显示设置：

转换为支持计算的Duration类型 #

修改自定义列代码，加入类型转换和错误捕获：

= let
    ageText = [Age],
    len = Text.Length(ageText),
    formattedText = if len = 7 then "0" & ageText
                    else if len = 5 then "00:" & ageText
                    else if len = 4 then "00:0" & ageText
                    else if len = 3 then "00:00" & ageText
                    else ageText,
    durationValue = try Duration.FromText(formattedText) otherwise null
in
    durationValue

配置Excel显示格式 #

将数据加载回Excel后，选中时长列，设置单元格格式为自定义，输入[h]:mm:ss，即可正常显示超过24小时的时长（如25:30:00）。

3. 解决转换报错问题 #

转换报错多因源数据存在不符合格式的内容（如含非数字/冒号字符），可通过两种方式处理：

• 错误捕获：用try...otherwise将转换失败的值设为null或自定义提示（如"格式错误"），避免流程中断
• 预清洗数据：提前清除无关字符，保留数字和冒号：

  = Text.Remove([Age], List.RemoveItems(Text.ToList([Age]), {"0".."9", ":"}))
  

  这段代码会过滤Age列中的非数字、非冒号字符，降低转换报错概率。

完整操作流程 #

1. 在Excel中通过「数据>从表格/范围」导入数据到Power Query
2. 点击「添加列>自定义列」，输入上述带错误处理的M语言代码
3. 若需计算时长，确认列类型为Duration；若仅需文本显示，直接输出formattedText
4. 加载数据回Excel，针对Duration类型列设置自定义格式[h]:mm:ss

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