Power Query Adding a row that sums up previous columns

Question

I'm trying to create a query that sums up a column of values and puts the sum as a new row in the same table. I know I can do this using the group function but it doesn't do it exactly as I need it to do. I'm trying to create an accounting Journal Entry and I need to calculate the offsetting for a long list of debits. I know this is accountant talk. Here's a sample of the table I am using.

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)

I have been using the Group By functions and grouping by Date with the result being the sum of Amount but that eliminates the previous rows and the four columns except Date. I need to keep all rows and columns, putting the sum in the same Amount column if possible. If the sum has to be in a new column, I can work with that as long as the other columns and rows remain. I also need to enter the GL Num, GL Name, and Location values for this sum row. These three values will not change. They will always be 3000, Accrual, All. The date will change based upon the date used in the actual data. I would prefer to do this all in Power Query (Get & Transform) if possible. I can do it via VBA but I'm trying to make this effortless for others to use.

Alexis Olson Alexis Olson · Accepted Answer · 2018-02-27T20:50:16

What you can do it calculate the accrual rows in a separate query and then append them.

Duplicate your query.
Group by Date and sum over Amount. This should return the following:

Date  Amount
1/31  11615

Multiply your Amount column by -1. (Transform > Standard > Multiply)
Add custom columns for GL Num, GL Name and Location with the fixed values you choose.

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

Append this table to your original. (Home > Append Queries.)

You can also roll this all up into a single query like this:

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"

Note that we are appending the last step with an earlier step in the query instead of referencing a different query.

Power Query Adding a row that sums up previous columns

1 Answers