Excel PowerQuery自定义列逐行应用公式的实现问题求助

核心问题说明 #

你之前尝试用GetValue()引用Excel单元格失败，是因为PowerQuery没有内置这个函数，正确做法是通过Excel.CurrentWorkbook()加载Excel参数值，再将Excel的IFS逻辑转换成PowerQuery的条件表达式。

步骤1：获取Excel中的参数值 #

先把公式用到的Program Dates工作表单元格值加载到PowerQuery中，推荐给这些单元格设置命名范围（比如B2命名为ProgramStartWeek、D2命名为ProgramEndWeek、G2命名为ProgramDuration），然后在PowerQuery中引用：

// 加载Excel中的参数命名范围
ProgramStartWeek = Excel.CurrentWorkbook(){[Name="ProgramStartWeek"]}[Content]{0}[Column1],
ProgramEndWeek = Excel.CurrentWorkbook(){[Name="ProgramEndWeek"]}[Content]{0}[Column1],
ProgramDuration = Excel.CurrentWorkbook(){[Name="ProgramDuration"]}[Content]{0}[Column1]

如果没设置命名范围，也可以直接引用工作表列（PowerQuery列索引从0开始，B列对应索引1）：

// 直接引用Program Dates工作表的单元格
ProgramDatesTable = Excel.CurrentWorkbook(){[Name="Program Dates"]}[Content],
ProgramStartWeek = ProgramDatesTable{0}[Column1], // B2单元格
ProgramEndWeek = ProgramDatesTable{0}[Column3], // D2单元格
ProgramDuration = ProgramDatesTable{0}[Column6] // G2单元格

步骤2：添加自定义列实现计算逻辑 #

在你的PowerQuery表中添加自定义列，把Excel的IFS逻辑转换成PowerQuery的if...else if结构，同时注意周数计算的一致性（Excel的WEEKNUM和PowerQuery的Date.WeekOfYear可能因周起始日不同有差异，需匹配）：

// 添加自定义列"Task Start Week Number"
AddCalculatedColumn = Table.AddColumn(你的源查询步骤名, "Task Start Week Number", each
    let
        // 转换Start Date为周数，这里用周一作为周起始，和Excel WEEKNUM(...,2)一致；如果是周日起始，去掉Day.Monday
        TaskStartWeek = Date.WeekOfYear([Start Date], Day.Monday)
    in
        if ProgramStartWeek < TaskStartWeek and TaskStartWeek < 54 then
            ProgramDuration - (ProgramEndWeek - (-53 + TaskStartWeek))
        else if TaskStartWeek < ProgramStartWeek then
            ProgramDuration - (ProgramEndWeek - (-53 + TaskStartWeek)) + 53
        else
            null // 处理周数>=54且不小于起始周的边界情况，可根据需求调整
)

可复用自定义函数写法 #

如果想写成可复用的自定义函数，正确格式是(参数) => 表达式，示例如下：

// 定义自定义函数
CalculateTaskWeek = (TaskStartWeek as number, ProgramStartWeek as number, ProgramEndWeek as number, ProgramDuration as number) =>
    if ProgramStartWeek < TaskStartWeek and TaskStartWeek < 54 then
        ProgramDuration - (ProgramEndWeek - (-53 + TaskStartWeek))
    else if TaskStartWeek < ProgramStartWeek then
        ProgramDuration - (ProgramEndWeek - (-53 + TaskStartWeek)) + 53
    else
        null

// 调用函数添加自定义列
AddCalculatedColumn = Table.AddColumn(你的源查询步骤名, "Task Start Week Number", each
    CalculateTaskWeek(
        Date.WeekOfYear([Start Date], Day.Monday),
        ProgramStartWeek,
        ProgramEndWeek,
        ProgramDuration
    )
)

关键注意点 #

• 周数一致性：确认Excel的WEEKNUM参数（比如是否用周一作为周起始），对应调整PowerQuery的Date.WeekOfYear的第二个参数。
• 边界处理：原公式未覆盖周数>=54且不小于项目起始周的情况，可根据实际需求补充else分支的逻辑。

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