Debt /credit

 

OK. I'm going to assume you're comfortable using Power Query in the absence of any further info.

 

Here's example code that will do what you need:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcvRCQAhDAPQVY58C03LiTpL6f5rnJ5CEQqBvNQdKKgkM6jCKkZTRHE8KcqTJuy5sL+cN/rmIcqLj7ftnO/vckPEBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Debt = _t, Credit = _t, Balance = _t, Timestamp = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Debt", Int64.Type}, {"Credit", Int64.Type}, {"Balance", Int64.Type}, {"Timestamp", type date}}),
    sortTimestamp = Table.Sort(chgTypes,{{"Timestamp", Order.Ascending}}),
    addIndex = Table.AddIndexColumn(sortTimestamp, "Index", 0, 1, Int64.Type),
    addBalanceChange = Table.AddColumn(addIndex, "acctBalanceChange", each if [Credit] <> null then [Credit] else [Debt] * -1, type number),
    runningTotal =
    List.Generate(
        () => [Index = 0, Total = addBalanceChange{0}[acctBalanceChange], Bal = Total],
        each [Index] < Table.RowCount(addBalanceChange),
        (previous) =>
            let
                newIndex = previous[Index] + 1
            in
                [
                    Index = newIndex,
                    Total = previous[Total] + addBalanceChange{newIndex}[acctBalanceChange],
                    Bal = List.Sum({previous[Bal], Total})
                ]
    ),
    addAcctBalanceCalc = Table.AddColumn(addBalanceChange, "acctBalanceCalc", each runningTotal{[Index]}[Total], type number),
    remOthCols = Table.SelectColumns(addAcctBalanceCalc,{"Timestamp", "Debt", "Credit", "acctBalanceCalc"})
in
    remOthCols

 

Example output:

 

Pete

Hi austin316 ,

 

Have you tried just pasting all of my example code over the default code in a new blank query? Does this query work for you when run on its own like this?

If yes, then your error is probably coming from somewhere in your replaced source code, or maybe a variable/anomaly in the real data that I wasn't aware of when I created this based on a very limited sample.

 

If my code works when its pasted into its own query, then I may need a larger, more representative example of your source data. The easiest way to provide this is:

1) Copy the data table that you have in Excel (max 3,000 cells, so max ~749 rows for 4 columns with headers).

2) Open Power Query and find 'Enter Data' on the Home tab. Paste your copied table in here.

3) Once the table has been generated in PQ, copy ALL the code from Advanced Editor for this new query and paste it all into code window ( </> button ) here.

 

This will allow me to really quickly recreate the larger example set at my end by copy/pasting into Advanced Editor.

 

If my code doesn't work when its pasted into its own query, then there's something else going on that we'll need to look into.

 

I've attached a PBIX with the query working below.

 

Pete