Debt /credit

[austin316]

Hello Experts.

Am having a bit of some challenge, trying to implement a two-column cash book logic 

The idea is to subtract or add [Debit amount] or  [Credit Amount] from  [Balance]

	Debt	Credit	Balance
	0	5000	5000
		5000	1000
	200	0	8000
	200	0	6000

However, am unable to write an expression that subtracts OR add automatically to the [balance] as a result of input on the [Debit amount] OR [Credit Amount] columns  .

[BA_Pete · Accepted solution]

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

[austin316]

let
Source = Excel.Workbook(File.Contents("C:\Users\HI\Documents\balance.xlsx"), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Debt = _t, Credit = _t, Balance = _t, Timestamp = _t], true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Timestamp", type date}, {"Debt", Int64.Type}, {"Credit", Int64.Type}, {"Balance", Int64.Type}}),

sortTimestamp = Table.Sort(#"Changed Type",{{"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

==============================================

Am currently using an excel sheet to try out the code sent. How ever I keep getting an error message.

Expression.Error: We cannot convert Type to Logical type.

Details:

    Value=[Type]

    Type=[Type]

[BA_Pete · Accepted solution]

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

[austin316]

Please is it possible to send the powerBi file from the above output with the codes so I could run on my powerBi application please.

[austin316]

WOw ! 
I will revert back as soon as possbile.

thanks.

[BA_Pete]

Hi @austin316 ,

How comfortable are you using Power Query?

Are you comfortable using functions like List.Generate and maintaining/understanding inline functions, or would you prefer something simpler/more intuitive but slightly less performant?

Also, does your source table also have a timestamp column showing exactly when each new D/C transaction was committed?

Pete

[austin316]

Yes , It has a time stamp . am actually pulling from sql database. 

[BA_Pete · Accepted solution]

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