Forum Discussion
Adding Custom Column To Obtain Prior Month Balance
JRParker my bad. I wanted to sort by date upon grouping but then changed my mind... Before I give up and commit a suicide, lets replace function f with the following
f = (tbl as table) as table => [sorted = Table.Sort(tbl, "Date"), // Sort the table by the "Date" column prior_month = {0} & List.RemoveLastN(sorted[Balance], 1), // Create a list of prior month balances by removing the last balance value and appending a 0 at the beginning out = Table.FromColumns(Table.ToColumns(sorted) & {prior_month}, Table.ColumnNames(sorted) & {"Prior Month"}) // Add the prior month balances as a new column named "Prior Month" ] [out]
JRParker my bad. I wanted to sort by date upon grouping but then changed my mind... Before I give up and commit a suicide, lets replace function f with the following
f = (tbl as table) as table =>
[sorted = Table.Sort(tbl, "Date"), // Sort the table by the "Date" column
prior_month = {0} & List.RemoveLastN(sorted[Balance], 1), // Create a list of prior month balances by removing the last balance value and appending a 0 at the beginning
out = Table.FromColumns(Table.ToColumns(sorted) & {prior_month}, Table.ColumnNames(sorted) & {"Prior Month"}) // Add the prior month balances as a new column named "Prior Month"
] [out]
Yeah baby! .... Bingo ..... thank you, thank you, thank you AlienSx!!! .....here is the result from Report View sorting by Date, including an added column Current Activity (the difference be between Balance and Prior month in the case of any month other than January; else Current Activity is Balance):
| Entity | Account Number | FY | Date | Balance | Prior Month | Current Activity |
| FVE | 4000-00-00 | 2022 | 3/31/2022 | ($2,330.08) | $0.00 | ($2,330.08) |
| FVE | 4000-00-00 | 2022 | 4/30/2022 | ($4,890.36) | ($2,330.08) | ($2,560.28) |
| FVE | 4000-00-00 | 2022 | 5/31/2022 | ($5,972.62) | ($4,890.36) | ($1,082.26) |
| FVE | 4000-00-00 | 2022 | 6/30/2022 | ($13,285.10) | ($5,972.62) | ($7,312.48) |
| FVE | 4000-00-00 | 2022 | 7/31/2022 | ($14,388.49) | ($13,285.10) | ($1,103.39) |
| FVE | 4000-00-00 | 2022 | 8/31/2022 | ($20,713.00) | ($14,388.49) | ($6,324.51) |
| FVE | 4000-00-00 | 2022 | 9/30/2022 | ($32,338.88) | ($20,713.00) | ($11,625.88) |
| FVE | 4000-00-00 | 2022 | 10/31/2022 | ($31,558.75) | ($32,338.88) | $780.13 |
| FVE | 4000-00-00 | 2022 | 11/30/2022 | ($47,740.27) | ($31,558.75) | ($16,181.52) |
| FVE | 4000-00-00 | 2022 | 12/31/2022 | ($49,812.30) | ($47,740.27) | ($2,072.03) |
| FVE | 4000-00-00 | 2023 | 1/31/2023 | ($2,910.00) | $0.00 | ($2,910.00) |
| FVE | 4000-00-00 | 2023 | 2/28/2023 | ($15,823.60) | ($2,910.00) | ($12,913.60) |
| FVE | 4000-00-00 | 2023 | 3/31/2023 | ($25,206.91) | ($15,823.60) | ($9,383.31) |
| FVE | 4000-00-00 | 2023 | 4/30/2023 | ($35,934.17) | ($25,206.91) | ($10,727.26) |
| FVE | 4000-00-00 | 2023 | 5/31/2023 | ($57,606.09) | ($35,934.17) | ($21,671.92) |
For the record, here is the code:
let
Source = #"Trial Balance",
// Step 1: Merge the "Trial Balance" table with the "Account Category" table based on the "Account Number" column
#"Merged Queries" = Table.NestedJoin(Source, {"Account Number"}, #"Account Category", {"Account Number"}, "Account Category", JoinKind.LeftOuter),
// Step 2: Expand the "Account Category" column to include the "Statement" column
#"Expanded Account Category" = Table.ExpandTableColumn(#"Merged Queries", "Account Category", {"Statement"}, {"Account Category.Statement"}),
// Step 3: Filter the rows to include only those with a "Statement" value of "Income Statement"
#"Filtered Rows" = Table.SelectRows(#"Expanded Account Category", each ([Account Category.Statement] = "Income Statement")),
// Step 4: Define a function "f" to perform further operations on each grouped table
f = (tbl as table) as table =>
[
sorted = Table.Sort(tbl, "Date"), // Sort the table by the "Date" column
prior_month = {0} & List.RemoveLastN(sorted[Balance], 1), // Create a list of prior month balances by removing the last balance value and appending a 0 at the beginning
out = Table.FromColumns(Table.ToColumns(sorted) & {prior_month}, Table.ColumnNames(sorted) & {"Prior Month"}) // Add the prior month balances as a new column named "Prior Month"
] [out],
// Step 5: Add a custom column "FY" to extract the fiscal year from the "Date" column
add_FY = Table.AddColumn(#"Filtered Rows", "FY", (x) => Date.Year(x[Date])),
// Step 6: Group the data by specific columns and apply the function "f" to each group
gr = Table.Group(add_FY, { "Entity", "Account Number", "Description", "Account # - Description", "FY" }, {{"all", each f(_)}}),
// Step 7: Expand the "all" column to include the "Date", "Balance", and "Prior Month" columns
expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"}),
// Step 8: Change the data types of columns to the appropriate types
#"Changed Type" = Table.TransformColumnTypes(expand, {{"Balance", Currency.Type}, {"Prior Month", Currency.Type}, {"Entity", type text}, {"Date", type date}}),
// Step 9: Add a custom column "Current Activity" to calculate the current month's activity based on the conditions
#"Added Custom" = Table.AddColumn(#"Changed Type", "Current Activity", each if Date.Month([Date]) = 1 then [Balance] else [Balance] - [Prior Month]),
// Step 10: Change the data type of the "Current Activity" column to Currency
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"Current Activity", Currency.Type}})
in
#"Changed Type1"