如何用SQL生成按月份拆分的Jobs工时聚合表用于PowerBI可视化

针对将Jobs表拆分为月度记录的需求，用Power Query做数据预处理是最高效的方式，步骤如下：

1. 导入并修正日期格式 #

• 将Jobs表导入Power Query编辑器
• 选中InitDate和EndDate列，右键选择更改类型→日期；若系统识别错格式（比如把dd/mm当成mm/dd），用自定义公式强制转换：

  = Date.FromText([InitDate], [Format="dd/MM/yyyy"])
  = Date.FromText([EndDate], [Format="dd/MM/yyyy"])
  

2. 生成任务覆盖的月度序列 #

添加自定义列，生成该任务从开始月份到结束月份的所有月份起始日期列表：

= List.Generate(
    () => Date.StartOfMonth([InitDate]),
    each _ <= Date.StartOfMonth([EndDate]),
    each Date.AddMonths(_, 1)
)

该公式自动适配跨月/同月任务，确保每个覆盖月份仅生成一条序列项。

3. 展开月度序列为单行记录 #

点击自定义列右侧的展开箭头，选择展开到新行，此时每个任务的每个覆盖月份都会单独生成一行记录。

4. 整理最终表结构 #

• 给展开后的日期列重命名为MonthStart，可额外添加格式化年月列（如MonthYear）方便可视化分组：

  = Text.From(Date.Year([MonthStart])) & "-" & Text.PadStart(Text.From(Date.Month([MonthStart])), 2, "0")
  

• 保留核心字段：Jobs、Desc、MonthlyHours、MonthYear
• 将表重命名为JobsAggregation并加载回PowerBI

边界情况说明 #

• 若任务起止日期在同一个月，仅生成一行记录
• 哪怕任务当月仅部分时间进行，依然会计入该月的MonthlyHours，匹配工时统计需求

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