Adding Custom Column To Obtain Prior Month Balance

correct

JRParker then replace gr step with the following 2 steps

    add_FY = Table.AddColumn(#"Filtered Rows", "FY", (x) => Date.Year(x[Date])),
    gr = 
        Table.Group(
            add_FY, { "Entity", "Account Number", "Description", "Account # - Description", "FY" }, 
            {{"all", each f(_)}}
        )

So after running the query and filtering to just one account, the default view in Power Query editor:

Here is the result in Report View with the default sorted by Date:

Don't understand the code enough to understand how the results end up as they do.

here is the code along with notes provided by ChatGPT prior to my filtering to one account:

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"), // Sort the table by the "Date" column
prior_month = {0} & List.RemoveLastN(tbl[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(tbl) & {prior_month}, Table.ColumnNames(tbl) & {"Prior Month"}) // Add the prior month balances as a new column named "Prior Month"
] [out],

// Step 6: 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 7: 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 8: 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 9: 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}})
in
// Step 10: Return the final transformed table
#"Changed Type"

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):

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"