Power BI中填充同一列日期区间内日期的技术实现咨询

原始表信息 #

表1：员工工时记录表（假设表名为EmployeeHours） #

表2：员工信息表（假设表名为EmployeeInfo，需包含以下字段） #

实现方案 #

方法一：Power Query预处理（推荐，性能更优） #

步骤1：生成连续日期表

1. 点击Power BI主页→输入数据，创建空查询并进入Power Query编辑器
2. 打开高级编辑器，替换为以下代码（按需调整起止日期）：

let
    StartDate = #date(2021, 11, 11),
    EndDate = #date(2022, 12, 31),
    DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
    #"转换为表" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"重命名列" = Table.RenameColumns(#"转换为表",{{"Column1", "Date"}})
in
    #"重命名列"

3. 将表命名为DateTable，关闭并应用

步骤2：为工时表添加生效周期

1. 加载EmployeeHours到Power Query，执行以下处理：

let
    源 = EmployeeHours,
    #"按EmpId分组" = Table.Group(源, {"EmpId"}, {{"所有行", each _, type table [Date=date, EmpId=text, Hours=number]}}),
    #"排序行" = Table.TransformColumns(#"按EmpId分组", {{"所有行", each Table.Sort(_,{{"Date", Order.Ascending}})}}),
    #"添加结束日期" = Table.TransformColumns(#"排序行", {{"所有行", each Table.AddColumn(_, "EndDate", (row) => 
        let
            CurrentIndex = Table.PositionOf(_, row),
            NextDate = if CurrentIndex < Table.RowCount(_)-1 then _{CurrentIndex+1}[Date] else #date(2022, 12, 31)
        in
            Date.AddDays(NextDate, -1)
    )}}),
    #"展开行" = Table.ExpandTableColumn(#"添加结束日期", "所有行", {"Date", "Hours", "EndDate"}, {"StartDate", "Hours", "EndDate"})
in
    #"展开行"

2. 将处理后的表命名为EmployeeHoursWithPeriod，关闭并应用

步骤3：合并关联生成目标表

1. 在Power Query中选择DateTable和EmployeeHoursWithPeriod，执行交叉合并
2. 添加自定义列筛选有效日期：[Date] >= [StartDate] and [Date] <= [EndDate]，保留该列为true的行并删除自定义列
3. 通过EmpId合并EmployeeInfo表，获取EmpName
4. 重命名Hours列为Contracthours，调整列顺序为Date、EmpId、EmpName、Contracthours，关闭并应用即可

方法二：DAX创建计算表 #

适合快速生成场景，数据量大时性能可能受影响：

目标表 = 
VAR DateRange = CALENDAR(MIN(EmployeeHours[Date]), MAX(EmployeeHours[Date]))
VAR EmployeePeriods = 
    ADDCOLUMNS(
        EmployeeHours,
        "EndDate",
        VAR CurrentEmp = [EmpId]
        VAR CurrentDate = [Date]
        RETURN
            COALESCE(
                MINX(FILTER(EmployeeHours, [EmpId] = CurrentEmp && [Date] > CurrentDate), [Date]) - 1,
                TODAY()
            )
    )
VAR CrossJoin = CROSSJOIN(DateRange, EmployeePeriods)
VAR FilteredRows = FILTER(CrossJoin, [Date] >= [Date] && [Date] <= [EndDate])
VAR WithEmpName = NATURALLEFTOUTERJOIN(FilteredRows, SELECTCOLUMNS(EmployeeInfo, "EmpId", [EmpId], "EmpName", [EmpName]))
RETURN
    SELECTCOLUMNS(WithEmpName, "Date", [Date], "EmpId", [EmpId], "EmpName", [EmpName], "Contracthours", [Hours])

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