Power Query分组后上期值相减及表追加前预处理方案

需求明确 #

先把逻辑理清楚：两张表要分别处理，按Code和Group作为分组依据，每组内按Date从小到大排序，然后计算当前行Amount减去上一行的Amount；如果是组里的第一行（没有上期数据），就用当前Amount减0，得到ExpectedColumn。

方法一：鼠标点选操作（适合新手） #

对每张表单独做以下操作，处理完再追加：

• 打开目标表的Power Query编辑器
• 排好日期顺序：选中Date列，点工具栏的「排序升序」，保证每组内日期是按先后排的
• 按Code+Group分组：点工具栏「分组依据」，按住Ctrl选Code和Group作为分组字段，新列名随便取（比如叫“分组数据”），操作选「所有行」，确定
• 加自定义列算结果：点「添加列」→「自定义列」，粘贴下面的公式：

  List.Generate(
      () => [Index=0, Current=_[分组数据]{0}[Amount], Previous=0, Result=_[分组数据]{0}[Amount]-0],
      each [Index] < List.Count(_[分组数据]),
      each [Index=[Index]+1, Current=_[分组数据]{[Index]}[Amount], Previous=_[分组数据]{[Index]-1}[Amount], Result=Current-Previous],
      each [Result]
  )
  

  这个公式会自动给每个分组生成对应的结果列表
• 展开数据：先点“分组数据”列的展开按钮，选「展开到新行」；再展开新生成的自定义列，就能得到每行的ExpectedColumn
• 清掉没用的列：把“分组数据”这类临时列删掉，保留需要的字段
• 另一张表重复上述步骤，最后点「追加查询」→「追加两个表」就行

方法二：写代码高效处理（适合进阶） #

直接在高级编辑器里改M代码，处理单张表（表2同理替换表名）：

let
    源 = 表1,
    按日期排序 = Table.Sort(源,{{"Date", Order.Ascending}}),
    按Code和Group分组 = Table.Group(按日期排序, {"Code", "Group"}, {{"数据行", each _, type table [Code=nullable number, Group=nullable text, Amount=nullable number, Date=nullable date]}}),
    添加计算列 = Table.AddColumn(按Code和Group分组, "ExpectedColumn", 
        (行) => 
            let
                分组数据 = 行[数据行],
                金额列表 = 分组数据[Amount],
                上期金额列表 = List.InsertRange(金额列表, 0, {0}) |> List.RemoveLastN(1),
                结果列表 = List.Zip({金额列表, 上期金额列表}) |> List.Transform( (x) => x{0} - x{1} )
            in
                结果列表
    ),
    展开结果 = Table.ExpandListColumn(添加计算列, "ExpectedColumn"),
    展开原始数据 = Table.ExpandTableColumn(展开结果, "数据行", {"Amount", "Date"}, {"Amount", "Date"}),
    调整列顺序 = Table.ReorderColumns(展开原始数据, {"Code", "Group", "Amount", "Date", "ExpectedColumn"})
in
    调整列顺序

• 代码逻辑：先按日期排序，再分组；给每组的金额列表前面插个0，再切掉最后一个元素，这样每个位置刚好对应上一行的金额（第一行对应0）；最后把当前金额和上期金额配对相减，展开后就是最终结果。

两张表都处理完后，执行追加操作合并即可。

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