Excel Power Query(M)：填充每日工资列及补全缺失薪资求助

步骤1：将「3 Month Salary」转换为可计算的数值类型 #

原数据中的薪资带千位分隔符，需先转为数值类型才能计算：

= Table.TransformColumns(源, {{"3 Month Salary", each if _ = "" then null else Number.FromText(Text.Replace(_, ",", "")), type number}})

逻辑说明：把空内容转为null，移除逗号后转成数值，避免后续计算报错。

步骤2：按员工分组填充缺失的薪资值 #

要保证同一员工的缺失薪资取之前的有效值，需按Name分组后在组内向下填充：

#"分组的行" = Table.Group(薪资转换后的表, {"Name"}, {{"GroupedData", each _, type table}}),
#"填充薪资列" = Table.AddColumn(#"分组的行", "FilledData", each Table.FillDown([GroupedData], {"3 Month Salary"})),
#"展开分组数据" = Table.ExpandTableColumn(#"填充薪资列", "FilledData", {"Date", "3 Month Salary", "Daily Wage"}, {"Date", "3 Month Salary", "Daily Wage"})

核心逻辑：仅在同一员工的行范围内填充有效值，不会跨员工产生干扰。

步骤3：计算「Daily Wage」列 #

结合日期判断周五的3倍薪资规则：

= Table.AddColumn(薪资填充后的表, "Daily Wage", each 
    let
        baseWage = [3 Month Salary] / 90,
        // 以周日为一周起始，周五对应返回值5；若以周一为起始，周五对应4，需调整判断条件
        dayOfWeek = Date.DayOfWeek([Date], Day.Sunday)
    in
        if dayOfWeek = 5 then baseWage * 3 else baseWage
)

完整可运行代码（适配你的测试数据） #

假设源表名为Table1，直接复制到Power Query高级编辑器即可使用：

let
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"更改列类型" = Table.TransformColumnTypes(源, {{"Date", type date}, {"Name", type text}, {"3 Month Salary", type text}, {"Daily Wage", type any}}),
    #"转换薪资为数值" = Table.TransformColumns(#"更改列类型", {{"3 Month Salary", each if _ = "" then null else Number.FromText(Text.Replace(_, ",", "")), type number}}),
    #"按员工分组" = Table.Group(#"转换薪资为数值", {"Name"}, {{"GroupedData", each _, type table [Date=date, Name=text, 3 Month Salary=number, Daily Wage=any]}}),
    #"填充缺失薪资" = Table.AddColumn(#"按员工分组", "FilledData", each Table.FillDown([GroupedData], {"3 Month Salary"})),
    #"展开数据" = Table.ExpandTableColumn(#"填充缺失薪资", "FilledData", {"Date", "3 Month Salary", "Daily Wage"}, {"Date", "3 Month Salary", "Daily Wage"}),
    #"计算每日工资" = Table.AddColumn(#"展开数据", "Daily Wage", each 
        let
            baseWage = [3 Month Salary] / 90,
            dayOfWeek = Date.DayOfWeek([Date], Day.Sunday)
        in
            if dayOfWeek = 5 then baseWage * 3 else baseWage
    )
in
    #"计算每日工资"

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