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]
BTW, the intent is create the custom column in Power Query; not Data View. Here is sample data; all but the last column is sample data from the table, with the last column the custom column with the expected results:
| Entity | Account Number | Date | Balance | Prior Month |
| FVE | 4000 | 3/31/2022 | ($2,330.08) | $0.00 |
| FVE | 4000 | 4/30/2022 | ($4,890.36) | ($2,330.08) |
| FVE | 4000 | 5/31/2022 | ($5,972.62) | ($4,890.36) |
| FVE | 4000 | 6/30/2022 | ($13,285.10) | ($5,972.62) |
| FVE | 4000 | 7/31/2022 | ($14,388.49) | ($13,285.10) |
| FVE | 4000 | 8/31/2022 | ($20,713.00) | ($14,388.49) |
| FVE | 4000 | 9/30/2022 | ($32,338.88) | ($20,713.00) |
| FVE | 4000 | 10/31/2022 | ($31,558.75) | ($32,338.88) |
| FVE | 4000 | 11/30/2022 | ($47,740.27) | ($31,558.75) |
| FVE | 4000 | 12/31/2022 | ($49,812.30) | ($47,740.27) |
| FVE | 4000 | 1/31/2023 | ($2,910.00) | ($49,812.30) |
| FVE | 4000 | 2/28/2023 | ($15,823.60) | ($2,910.00) |
| FVE | 4000 | 3/31/2023 | ($25,206.91) | ($15,823.60) |
| FVE | 4000 | 4/30/2023 | ($35,934.17) | ($25,206.91) |
| FVE | 4000 | 5/31/2023 | ($57,606.09) | ($35,934.17) |
Know there are other Entities and Account Numbers in the table. 🙂
JRParker Here is one way of doing it. However, for additional Entities and Account Numbers you may need to modify things a bit but perhaps not.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLJjcMwDEV7MXIYAgTNRQtVQKaEuQTuv40oiDGxSOQkCfB7X/7i47H9/t033Aozz8V2k11Zde5/bopmTOwwT7e54e3AAJTd+AMU9MFkDQKesLrkVBxdqSkEScLakiaG6pWEIVgS15c4KWjuVAZETQJ97YOxi80eIHoSOJab2qsKJ3eIngQKL5EmWKtTrxBFmZT1LTr2wqQdoiiTumSWgS5Kdv7mRZTJE7TzwYfwfz0XTeJ0V/9wUtHVqDEETeJszauo3GgIRM3XOX2DNmfFCsnZzcXzdVLfYO3Y5pd8js7FcxxP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Account Number" = _t, Date = _t, Balance = _t, #"Prior Month" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Account Number", Int64.Type}, {"Date", type date}, {"Balance", Currency.Type}, {"Prior Month", Currency.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Prior Month"}), #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index.1", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Balance"}, {"Added Index1.Balance"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Added Index1",null,0,Replacer.ReplaceValue,{"Added Index1.Balance"}) in #"Replaced Value"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 expandJust saw this post AlienSx ... let me experiment and advise... thanks... Jim
