Excel PowerQuery动态列数处理求助：空值替换与月度差值计算

一、动态替换所有月份列的空值为0 #

原代码通过固定列序号指定处理范围，无法适配列数增长。只需改为动态获取所有透视后的月份列即可解决：

假设透视后前2列为固定非月份列（如类别、产品名称等），用List.Skip跳过固定列，自动获取所有动态月份列：

= Table.ReplaceValue(
    #"Pivoted Column",
    null,
    0,
    Replacer.ReplaceValue,
    List.Skip(Table.ColumnNames(#"Pivoted Column"), 2) // 跳过前2个固定列，处理所有月份列
)

• 若固定列数量不是2，修改List.Skip的第二个参数即可（比如前1列固定就写1）。

二、自动添加月度变化幅度计算列 #

要实现逐月列间的变化幅度计算，需先确保月份列按时间排序，再循环添加计算列：

完整代码示例 #

let
    // 引用透视后的表
    PivotedTable = #"Pivoted Column",
    // 1. 替换空值为0
    ReplacedNulls = Table.ReplaceValue(
        PivotedTable,
        null,
        0,
        Replacer.ReplaceValue,
        List.Skip(Table.ColumnNames(PivotedTable), 2)
    ),
    // 2. 获取并按时间排序月份列（假设列名是标准日期格式，如"2024-01"）
    MonthColumns = List.Skip(Table.ColumnNames(ReplacedNulls), 2),
    SortedMonthColumns = List.Sort(MonthColumns, (a,b) => Date.FromText(a) > Date.FromText(b) ? 1 : -1),
    // 3. 循环添加月度变化幅度列
    AddedChangeColumns = List.Accumulate(
        List.Skip(SortedMonthColumns, 1), // 从第二个月份开始计算
        ReplacedNulls,
        (table, currentMonth) => 
            let
                prevMonth = SortedMonthColumns{List.PositionOf(SortedMonthColumns, currentMonth)-1},
                // 计算变化幅度，处理上月为0的情况避免报错
                changeRate = if Record.Field(_, prevMonth) = 0 then 0 else (Record.Field(_, currentMonth) - Record.Field(_, prevMonth)) / Record.Field(_, prevMonth) * 100
            in
                Table.AddColumn(table, currentMonth & " 变化幅度(%)", changeRate, type number)
    )
in
    AddedChangeColumns

关键说明 #

• 若月份列名不是标准日期格式（如"2024年2月"），需调整排序逻辑，比如提取年份和月份数字进行排序：

  SortedMonthColumns = List.Sort(MonthColumns, (a,b) => 
      let
          aYear = Number.From(Text.BeforeDelimiter(a, "年")),
          aMonth = Number.From(Text.BetweenDelimiters(a, "年", "月")),
          bYear = Number.From(Text.BeforeDelimiter(b, "年")),
          bMonth = Number.From(Text.BetweenDelimiters(b, "年", "月"))
      in
          if aYear <> bYear then aYear - bYear else aMonth - bMonth
  )
  

• 变化幅度公式可根据需求调整，比如改为绝对差值Record.Field(_, currentMonth) - Record.Field(_, prevMonth)。

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