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]
JRParker Here is the PBIX file (attached below signature).
Greg, thanks for providing the pbix file; very helpful. While your pbix code is right on with the correct results, I've added what I believe to be the relevant lines to the query of my existing table and have incorrect results. Can you advise what I've done wrong? Note there are two Merged Queries:
let
// note this table is first a reference to a Trial Balance table
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")),
// this is the point where we have the existing table and want to add the custom column
// added these steps from your code (along with the ',' at the end of the previous line
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
#"Merged Queries-2" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries-2", "Added Index1", {"Balance"}, {"Added Index1.Balance"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Added Index1",null,0,Replacer.ReplaceValue,{"Added Index1.Balance"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Date", Order.Ascending}}),
// added these over and above your code to remove unncessary columns and rename the custom column
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Account Category.Statement", "Index", "Index.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added Index1.Balance", "Prior Month Balance"}})
in
#"Renamed Columns"
Let me know if you need to know what kind of results other than incorrect results.
Pretty sure it is not relevant in that it they are not referenced in your code, but the Entity and Account Number columns are different Data Types Any any Text, respectively, while your code has Text and whole number, respectively.
Don't know how to add an attachment, but here is an excerpt of the table, including the custom column Prior Month Balance:
Entity Account Date Balance Prior Month Balance
Entity 2 4000 3/31/2022 ($2,275.00) ($65.86)
Entity 1 4000-00-00 3/31/2022 ($2,330.08) $6,250.00
Entity 1 4000-00-10 3/31/2022 ($32,257.70) ($2,330.08)
Entity 1 4000-00-50 3/31/2022 ($58,840.50) ($32,257.70)
Entity 1 4000-00-70 3/31/2022 ($121,970.09) ($58,840.50)
Entity 2 4005 3/31/2022 ($69,298.66) ($2,275.00)
Entity 2 4010 3/31/2022 $140.00 ($69,298.66)
Entity 1 4010-00-00 3/31/2022 ($46,717.71) ($121,970.09)
Entity 1 4010-00-50 3/31/2022 ($67,874.32) ($46,717.71)
Entity 1 4100-00-00 3/31/2022 ($13,963.11) ($67,874.32)
Entity 1 4200-00-00 3/31/2022 $21,426.84 ($13,963.11)
Entity 1 4400-00-00 3/31/2022 $122.47 $21,426.84
INTER 4999 3/31/2022 ($18,720.29) ($1,500.00)
Entity 2 5000 3/31/2022 $1,696.09 $140.00
Entity 1 5000-00-00 3/31/2022 $17,493.21 $122.47
Entity 1 5000-00-10 3/31/2022 $11,027.87 $17,493.21
Entity 1 5000-00-50 3/31/2022 $77,614.45 $11,027.87
Entity 1 5000-00-70 3/31/2022 $84,398.13 $77,614.45
Entity 1 5000-00-90 3/31/2022 ($4,883.34) $84,398.13
Entity 2 5010 3/31/2022 $20.00 $1,696.09
Entity 2 5200 3/31/2022 $8,440.00 $424.50
Entity 2 5250 3/31/2022 $316.50 $8,440.00
Entity 1 5300-00-00 3/31/2022 ($17,385.99) ($4,883.34)
Entity 2 5305 3/31/2022 $424.50 $20.00
Entity 2 5315 3/31/2022 $360.00 $316.50
Entity 1 5320-00-00 3/31/2022 ($1,739.56) ($17,385.99)
Entity 1 5400-00-00 3/31/2022 $32,684.58 ($1,739.56)
Entity 1 5400-00-10 3/31/2022 $5,663.44 $32,684.58
Entity 1 5400-00-20 3/31/2022 $3,986.02 $5,663.44
Entity 1 5400-00-30 3/31/2022 $3,048.88 $3,986.02
Entity 1 5400-00-40 3/31/2022 $3,534.82 $3,048.88Don't see how to add an attachment of sample data, but here is an excerpt for context:
Entity Account Date Balance Prior Month
Entity 2 4000 3/31/2022 ($2,275.00) ($65.86)
Entity 1 4000-00-00 3/31/2022 ($2,330.08) $6,250.00
Entity 1 4000-00-10 3/31/2022 ($32,257.70) ($2,330.08)
Entity 1 4000-00-50 3/31/2022 ($58,840.50) ($32,257.70)
Entity 1 4000-00-70 3/31/2022 ($121,970.09) ($58,840.50)
Entity 2 4005 3/31/2022 ($69,298.66) ($2,275.00)
Entity 2 4010 3/31/2022 $140.00 ($69,298.66)
Entity 1 4010-00-00 3/31/2022 ($46,717.71) ($121,970.09)
Entity 1 4010-00-50 3/31/2022 ($67,874.32) ($46,717.71)
Entity 1 4100-00-00 3/31/2022 ($13,963.11) ($67,874.32)
Entity 1 4200-00-00 3/31/2022 $21,426.84 ($13,963.11)
Entity 1 4400-00-00 3/31/2022 $122.47 $21,426.84
INTER 4999 3/31/2022 ($18,720.29) ($1,500.00)
Entity 2 5000 3/31/2022 $1,696.09 $140.00
Entity 1 5000-00-00 3/31/2022 $17,493.21 $122.47
Entity 1 5000-00-10 3/31/2022 $11,027.87 $17,493.21
Entity 1 5000-00-50 3/31/2022 $77,614.45 $11,027.87