Forum Discussion

JRParker's avatar
JRParker
Helper III
3 years ago
Solved

Adding Custom Column To Obtain Prior Month Balance

Trying to create a custom column 'Prior Month Balance' by looking at the [Balance] column of the prior month with the same Entity and Account Number.     Here are the relevant columns of the table:...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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's avatar
JRParker
Helper III
3 years ago

Know there are other Entities and Account Numbers in the table. 🙂

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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"