Power Query计算成交量变化百分比对齐异常及插入Null值问题

问题核心 #

原Excel中从第二行开始计算成交量变化百分比（公式=IF(ISERROR((G3/G2-1)*100),"",(G3/G2-1)*100)），但Power Query实现时第一行就出现计算值，导致与原始数据对齐异常，需要让第一行显示Null，从第二行开始正常计算。

修正后的Power Query代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_BTCUSD_M30"]}[Content],
    // 添加从0开始的索引，标记行位置
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    // 自连接匹配上一行数据：当前行索引 = 上一行索引 + 1
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index", {"Index"}, "PrevRow", JoinKind.LeftOuter, (curr, prev) => curr[Index] = prev[Index] + 1),
    // 展开上一行的TickVol字段
    #"Expanded PrevRow" = Table.ExpandTableColumn(#"Merged Queries", "PrevRow", {"<TICKVOL>"}, {"Prev_TICKVOL"}),
    // 计算百分比：第一行直接返回Null，其余行计算当前/上一行-1，同时捕获错误
    #"Added %VOLUME CHANGE" = Table.AddColumn(#"Expanded PrevRow", "%VOLUME CHANGE", each if [Index] = 0 then null else try ([<TICKVOL>]/[Prev_TICKVOL] - 1) otherwise null),
    // 移除辅助列
    #"Removed Columns" = Table.RemoveColumns(#"Added %VOLUME CHANGE",{"Index", "Prev_TICKVOL"}),
    // 转换为百分比格式
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"%VOLUME CHANGE", Percentage.Type}})
in
    #"Changed Type"

关键说明 #

1. 索引逻辑：从0开始添加索引，直接通过Index=0判断第一行，返回Null实现对齐
2. 自连接匹配：通过自定义连接条件确保当前行仅匹配上一行的TickVol数据，避免逻辑混乱
3. 错误处理：用try...otherwise替代Excel的ISERROR，处理除零等异常情况
4. 格式转换：最后将计算列转为百分比类型，与Excel效果一致

原代码问题分析 #

可用代码的问题 #

• 计算逻辑反向：使用了上一行TickVol/当前TickVol -1，与Excel的当前/上一行 -1逻辑不符
• 第一行无判断：未处理无前置行的情况，导致第一行出现错误计算值

不可用代码的问题 #

• 最终步骤错误调用原始表：Table.InsertRows(Source, 0, ...)使用了未处理的Source表，前面所有计算步骤全部失效，返回的只是插入了一行Null的原始数据，完全达不到需求

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