Power Query 动态按年份拆分计算两个日期间各年度天数

需求说明 #

• 数据源包含Start_Date（开始日期）、End_Date（结束日期）两个日期列
• 需根据全表日期的最小年份、最大年份动态新增列，列名格式为Days_年份
• 列值为对应行的日期区间落在该自然年内的实际天数，非累计值

原有代码缺陷 #

原有实现直接用当年年末减行起始日期计算，返回的是从开始日期到当年年末的累计天数，没有截取日期区间和自然年的重叠部分，且通过总间隔天数除以365推算年份范围存在边界误差，原有代码如下：

let 
    yearRange = Number.RoundUp(Value.Divide(Duration.Days(List.Max(#"Changed Type1"[End_Date]) - List.Min(#"Changed Type1"[Start_Date])), 365)),
    minYear = Date.Year(List.Min(#"Changed Type1"[Start_Date])),
    maxYear = Date.Year(List.Max(#"Changed Type1"[End_Date])) 
in
List.Accumulate(List.Numbers(minYear, yearRange), #"Changed Type1", (state, current) => Table.AddColumn(state, "Days_" & Number.ToText(current), each Duration.Days(Date.EndOfYear(Date.FromText(Number.ToText(current) & "/01/01")) - [Start_Date])))

正确实现代码 #

核心计算逻辑：对每个待统计年份，先取行日期区间与当年自然年区间的交集，无交集则天数为0，有交集则计算交集中的实际天数；年份范围直接通过最大、最小年份差值生成，避免换算误差。
将原有步骤替换为以下代码即可（假设上一步骤名称为#"Changed Type1"）：

let
    sourceTable = #"Changed Type1",
    minYear = Date.Year(List.Min(sourceTable[Start_Date])),
    maxYear = Date.Year(List.Max(sourceTable[End_Date])),
    yearList = List.Numbers(minYear, maxYear - minYear + 1),
    resultTable = List.Accumulate(
        yearList,
        sourceTable,
        (state, currentYear) => Table.AddColumn(
            state,
            "Days_" & Number.ToText(currentYear),
            each 
                let
                    yearStart = #date(currentYear, 1, 1),
                    yearEnd = #date(currentYear, 12, 31),
                    overlapStart = List.Max({[Start_Date], yearStart}),
                    overlapEnd = List.Min({[End_Date], yearEnd}),
                    dayCount = if overlapStart > overlapEnd then 0 else Duration.Days(overlapEnd - overlapStart) + 1
                in
                    dayCount,
            Int64.Type
        )
    )
in
    resultTable

逻辑说明 #

• 年份列表直接通过maxYear - minYear + 1生成，覆盖所有涉及的自然年，不会出现漏年、多年问题
• 使用#date()构造每年的起止日期，避免字符串转日期的区域格式兼容问题，性能更优
• 每列计算时先取重叠区间的起止点：重叠起点为行开始日期、当年第一天的较晚值，重叠终点为行结束日期、当年最后一天的较早值
• 重叠起点晚于重叠终点时说明当前行日期区间与该年无交集，返回0；其余情况按结束日期减开始日期+1计算实际天数（包含起止当日，若需算头不算尾可去掉+1）

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