Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors