Adding Custom Column To Obtain Prior Month Balance

Just saw this post AlienSx ... let me experiment and advise... thanks... Jim

Wicked!                                       AlienSx = 1 ChatGPT = 0

One issue that surfaces presumably having to do with the original source of my table.  First, below is a summary of the Power Query code with your code embedded and an explanation of each line by ChatGPT. Note that prior to your code commencing at Step 5, we brought in the Trial Balance table, merged a column named Statement, and filtered the Statement column to only "Income Statement" accounts (vs "Balance Sheet" accounts).  Interestingly, the final results of this query include both "Income Statement" and "Balance Sheet" accounts where only "Income Statement" accounts should result.  Can you advise how to resolve this?  

let
// Step 1: Define the data source as the "Trial Balance" table
Source = #"Trial Balance",
// Step 2: 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 3: Expand the "Account Category" column to include the "Statement" column
#"Expanded Account Category" = Table.ExpandTableColumn(#"Merged Queries", "Account Category", {"Statement"}, {"Account Category.Statement"}),
// Step 4: 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 5: Define a function "f" to perform further operations on each grouped 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],
// Step 6: Group the data by specific columns and apply the function "f" to each group
gr = Table.Group(Source, {"Entity", "Account Number", "Description", "Account # - Description" }, {{"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 the "Balance" and "Prior Month" columns to Currency
#"Changed Type" = Table.TransformColumnTypes(expand,{{"Balance", Currency.Type}, {"Prior Month", Currency.Type}})
in
// Step 9: Return the final transformed table
#"Changed Type"

An issue most likely due to embedding your code into the pre-existing code, or not articulating the data structure. Here is an excerpt of 2 Acct #s:

1) Note the data starts with 3/31/22 

2) There may be an issue if the prior month is in a prior year

3 ) Some of the Prior Month results are seemingly correct (highlighted in red) while other months are not

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: