如何将PowerBI的自然年度日期重叠天数公式转换为PowerQuery代码

实现步骤 #

1. 打开PowerBI数据视图，选中目标表DATA
2. 点击顶部菜单栏的转换数据，进入PowerQuery编辑器
3. 切换到添加列选项卡，选择自定义列
4. 在自定义列公式框中粘贴以下M语言代码，将新列命名为OVERLAP days 2021：

= let
    YearStart = #date(2021,1,1),
    YearEnd = #date(2021,12,31),
    // 处理日期字段（若包含时间部分则转换为纯日期）
    RecordStart = Date.From([StartDate]),
    RecordEnd = Date.From([EndDate]),
    // 计算重叠区间的起止日期
    OverlapStart = List.Max({YearStart, RecordEnd}),
    OverlapEnd = List.Min({YearEnd, RecordStart}),
    // 计算重叠天数
    Days = Duration.Days(OverlapEnd - OverlapStart) + 1
in
    // 若天数为负则返回0（无重叠）
    if Days > 0 then Days else 0

代码逻辑说明 #

• YearStart/YearEnd：定义目标自然年的起止日期，对应原DAX公式中的DATE(2021,1,1)和DATE(2021,12,31)
• RecordStart/RecordEnd：将表中的日期字段转换为纯日期格式，避免时间部分干扰计算
• OverlapStart：取年度开始日期与记录结束日期的最大值，对应DAX中的MAX(DATE(2021,1,1),'DATA'[EndDate].[Date])
• OverlapEnd：取年度结束日期与记录开始日期的最小值，对应DAX中的MIN(DATE(2021,12,31),'DATA'[StartDate].[Date])
• 最后通过Duration.Days计算天数差并加1，若结果为负则返回0（表示无重叠）

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