Adding Custom Column To Obtain Prior Month Balance

Sorry, such a nubie at this... so after and outside the query, I need to group the data as your stated in your original post:

"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"

I need to explore grouping in Data View and/or Report View.

Almost forgot the bigger objective.  Determining the Prior Month was really the first of two steps in my effort to ultimately calculate the "Current Month Activity".  In accounting, the Income Statement (not the Balance Sheet) Trial Balance accounts are a cumlative balance starting at the beginning of the fiscal year (in this case the calendar year where January is month 1), and resets at the end of the year.  So, with the exception of month 1, the difference between the Balance and Prior Month would be the Current Month Activity.  In the case of month 1, the Balance is the Current Month Activity.  For example, Balance in month 1 is 1000, Balance in month 2 is 1600, then the Currnet Month Activity for month 1 and month 2 would be 1000 and 600, respectively.

With the Prior Month determined, the logic would be:

IF month =1 THEN Current Month Activity = Balance ELSE Current Month Activity = Balance - Prior Month.

After Current Month Activity was determined, then the Prior Month column was going to be remvoved as no longer needed.  

I'm not sure how to do this, particuarly in light of the grouping that needs to be done to get it right.  Can you advise?  

AlienSx, I don't understand where, when, or how you're grouping to get the results.

This is the Power Query with your code embedded; presumably I did this correctly:

let
Source = #"Trial Balance",
#"Merged Queries" = Table.NestedJoin(Source, {"Account Number"}, #"Account Category", {"Account Number"}, "Account Category", JoinKind.LeftOuter),
#"Expanded Account Category" = Table.ExpandTableColumn(#"Merged Queries", "Account Category", {"Statement"}, {"Account Category.Statement"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Account Category", each ([Account Category.Statement] = "Income Statement")),
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(#"Filtered Rows", {"Entity", "Account Number", "Description", "Account # - Description" }, {{"all", each f(_)}}),
expand = Table.ExpandTableColumn(gr, "all", {"Date", "Balance", "Prior Month"}, {"Date", "Balance", "Prior Month"}),
#"Changed Type" = Table.TransformColumnTypes(expand,{{"Balance", Currency.Type}, {"Prior Month", Currency.Type}, {"Entity", type text}, {"Date", type date}})
in
#"Changed Type"

Below is the default output after the query. Note the prior month values equate to the previous row values , but the previous row isn't necessarily the previous month:

This is what it looks like after sorting by Date: