cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
austin316
Regular Visitor

Debt /credit

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]

 

     
 DebtCreditBalance 
 050005000 
  50001000 
 20008000 
 20006000 
     

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  .

 

2 ACCEPTED SOLUTIONS

 

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:

BA_Pete_0-1669394167404.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
austin316
Regular Visitor

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]

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




austin316
Regular Visitor

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
Regular Visitor

WOw ! 
I will revert back as soon as possbile.

 

thanks.

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

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:

BA_Pete_0-1669394167404.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors