Forum Discussion
Solved
Adding Custom Column To Obtain Prior Month Balance
Trying to create a custom column 'Prior Month Balance' by looking at the [Balance] column of the prior month with the same Entity and Account Number. Here are the relevant columns of the table:...
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]
hi, JRParker group your data by Entity and Account Number and then do whatever you want to each group of data. If you have 1 balance per month per every Entity & Account then try this
let
Source = your_table,
f = (tbl as table) as table =>
[sorted = Table.Sort(tbl, "Date"),
prior_month = {0} & List.RemoveLastN( tbl[Balance], 1),
out = Table.FromColumns( Table.ToColumns(tbl) & {prior_month}, Table.ColumnNames(tbl) & {"Prior Month"})] [out],
gr = Table.Group(Source, {"Entity", "Account Number"}, {{"all", each f(_)}}),
expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"})
in
expand
Ok, there is an issue that likely has something to do with the sorting or timing thereof in Power Query. Here is a snapshot of the table after the query is run; note the default date sequence and the Prior Month values relative to the prior row (?) and it gets out of whack at the change of the year. Then note what happens after sorting on the Date column:
| Entity | Account Number | Date | Balance | Prior Month |
| FVE | 4000-00-00 | 1/31/2023 | $ (2,910.00) | $ - |
| FVE | 4000-00-00 | 2/28/2023 | $ (15,823.60) | $ (2,910.00) |
| FVE | 4000-00-00 | 3/31/2023 | $ (25,206.91) | $ (15,823.60) |
| FVE | 4000-00-00 | 4/30/2023 | $ (35,934.17) | $ (25,206.91) |
| FVE | 4000-00-00 | 12/31/2022 | $ (49,812.30) | $ (35,934.17) |
| FVE | 4000-00-00 | 5/31/2023 | $ (57,606.09) | $ (49,812.30) |
| FVE | 4000-00-00 | 11/30/2022 | $ (47,740.27) | $ (57,606.09) |
| FVE | 4000-00-00 | 10/31/2022 | $ (31,558.75) | $ (47,740.27) |
| FVE | 4000-00-00 | 9/30/2022 | $ (32,338.88) | $ (31,558.75) |
| FVE | 4000-00-00 | 8/31/2022 | $ (20,713.00) | $ (32,338.88) |
| FVE | 4000-00-00 | 7/31/2022 | $ (14,388.49) | $ (20,713.00) |
| FVE | 4000-00-00 | 6/30/2022 | $ (13,285.10) | $ (14,388.49) |
| FVE | 4000-00-00 | 5/31/2022 | $ (5,972.62) | $ (13,285.10) |
| FVE | 4000-00-00 | 3/31/2022 | $ (2,330.08) | $ (5,972.62) |
| FVE | 4000-00-00 | 4/30/2022 | $ (4,890.36) | $ (2,330.08) |
| Entity | Account Number | Date | Balance | Prior Month |
| FVE | 4000-00-00 | 3/31/2022 | $ (2,330.08) | $ (5,972.62) |
| FVE | 4000-00-00 | 4/30/2022 | $ (4,890.36) | $ (2,330.08) |
| FVE | 4000-00-00 | 5/31/2022 | $ (5,972.62) | $ (13,285.10) |
| FVE | 4000-00-00 | 6/30/2022 | $ (13,285.10) | $ (14,388.49) |
| FVE | 4000-00-00 | 7/31/2022 | $ (14,388.49) | $ (20,713.00) |
| FVE | 4000-00-00 | 8/31/2022 | $ (20,713.00) | $ (32,338.88) |
| FVE | 4000-00-00 | 9/30/2022 | $ (32,338.88) | $ (31,558.75) |
| FVE | 4000-00-00 | 10/31/2022 | $ (31,558.75) | $ (47,740.27) |
| FVE | 4000-00-00 | 11/30/2022 | $ (47,740.27) | $ (57,606.09) |
| FVE | 4000-00-00 | 12/31/2022 | $ (49,812.30) | $ (35,934.17) |
| FVE | 4000-00-00 | 1/31/2023 | $ (2,910.00) | $ - |
| FVE | 4000-00-00 | 2/28/2023 | $ (15,823.60) | $ (2,910.00) |
| FVE | 4000-00-00 | 3/31/2023 | $ (25,206.91) | $ (15,823.60) |
| FVE | 4000-00-00 | 4/30/2023 | $ (35,934.17) | $ (25,206.91) |
| FVE | 4000-00-00 | 5/31/2023 | $ (57,606.09) | $ (49,812.30) |