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