Calculate Account Balance with missing Transaction based Balance

Hi AlainB 

Please see the M code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0lXUMDIJlXmpODoBKBOCC1qDg/T8FQKVYHXYcRpo4khA4jTB1QRYYGSBqT8erQNTFA0QfipWC4yghZhymmq1Ix7DDCZQeUSsOrA6oIbJOlGYhMx+8mLCGVgV8HcmxYGoM4mRgaTJA1GOONPiMsOlCiwQLMSUWxIxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Debit = _t, Credit = _t, Balance = _t, Category = _t, Person = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Debit", Int64.Type}, {"Credit", Int64.Type}, {"Balance", Int64.Type}, {"Category", type text}, {"Person", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "calcBalance", each [Balance], Int64.Type ),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"calcBalance"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"calcBalance"}, {{
        "tbl",  ( _partition ) => 
            let 
                sort = Table.Sort( _partition, { { "Date", Order.Ascending}, { "Category", Order.Ascending } } ),
                addIndex = Table.AddIndexColumn( sort, "Index", 0, 1 ),
                addColumn = Table.AddColumn( addIndex, "RT", each [calcBalance] -
                    List.Sum(
                        Table.AddColumn( 
                            Table.SelectRows( 
                                Table.ReplaceValue( addIndex, null, 0, Replacer.ReplaceValue, { "Debit", "Credit" } ), 
                                let _ind = [Index] in each [Index] > _ind 
                            ),
                            "RT", each [Credit] + [Debit]
                        )[RT] 
                    ) 
                )  
            in addColumn, 
        type table [Date=date, Debit=number, Credit=number, Balance=number, Category=text, Person=text, calcBalance=number, RT=number]
        }}
    ),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Date", "Debit", "Credit", "Balance", "Category", "Person", "RT"}, {"Date", "Debit", "Credit", "Balance", "Category", "Person", "RT Balacne"}),
    #"Replaced Value" = Table.ReplaceValue( #"Expanded tbl", null, each [Balance], Replacer.ReplaceValue, { "RT Balacne" } ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"RT Balacne", type number}})
in
    #"Changed Type1"

Let me know if you need any help.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.