excel - Power Query 添加总结前列的行

Question

我正在尝试创建一个汇总一列值并将总和作为新行放在同一个表中的查询。我知道我可以使用 group 函数来做到这一点，但它并没有完全按照我的需要去做。我正在尝试创建一个会计日记帐分录，我需要计算一长串借方的抵消。我知道这是会计谈话。这是我正在使用的表格示例。

Date GL Num  GL Name  Location  Amount
1/31 8000    Payroll  Office    7000.00
1/31 8000    Payroll  Remote    1750.00
1/31 8000    Payroll  City      1800.00
1/31 8010    Taxes    Office    600.00
1/31 8010    Taxes    Remote    225.00
1/31 8010    Taxes    City      240.00
1/31 3000    Accrual  All       (This needs to be the negative sum of all other rows)

我一直在使用 Group By 函数并按 Date 分组，结果是 Amount 的总和，但这消除了前面的行和除 Date 之外的四列。我需要保留所有行和列，如果可能的话，将总和放在同一个 Amount 列中。如果总和必须在一个新列中，只要其他列和行仍然存在，我就可以使用它。我还需要为此总和行输入 GL Num、GL Name 和 Location 值。这三个值不会改变。它们将始终为 3000、Accrual、All。日期将根据实际数据中使用的日期而变化。如果可能的话，我宁愿在 Power Query (Get & Transform) 中完成这一切。我可以通过 VBA 做到这一点，但我试图让其他人轻松使用它。

Answer 1

你可以做什么它计算一个单独的查询中的应计行，然后附加它们。

复制您的查询。

按 Date 分组和总和 Amount .这应该返回以下内容:

Date  Amount
1/31  11615

乘以您的Amount按 -1 列。 (变换>标准>乘)

为 GL Num 添加自定义列, GL Name和 Location使用您选择的固定值。

Date  Amount  GL Num  GL Name  Location
1/31   11615    3000  Accrual       All

将此表附加到您的原件。 (首页 > 追加查询。)

您还可以将所有这些汇总到一个查询中，如下所示:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    OriginalTable = Table.TransformColumnTypes(Source,{{"Date", type date}, {"GL Num", Int64.Type}, {"GL Name", type text}, {"Location", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(OriginalTable, {"Date"}, {{"Amount", each List.Sum([Amount]), type number}}),
    #"Multiplied Column" = Table.TransformColumns(#"Grouped Rows", {{"Amount", each _ * -1, type number}}),
    #"Added Custom" = Table.AddColumn(#"Multiplied Column", "GL Num", each 3000),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "GL Name", each "Accrual"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Location", each "All"),
    #"Appended Query" = Table.Combine({OriginalTable, #"Added Custom2"})
in
    #"Appended Query"

请注意，我们在查询中附加了最后一步，而不是引用不同的查询。