Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I am trying to calculate the missing Account Balance per Transaction, as the CSV Export I get looks like the table below:
I only get the balance once per day and not for every transaction.
fTableTransactions Date Debit Credit Balance Category Person 01.01.2019 -10.00 a Person 1 01.01.2019 -20.00 b Person 2 01.01.2019 100.00 c Person 2 01.01.2019 -40.00 1000.00 d Person 1 02.01.2019 -50.00 e Person 2 02.01.2019 -40.00 f Person 2 02.01.2019 50.00 960.00 g Person 1 02.01.2019 -20.00 h Person 1 02.01.2019 -10.00 930.00 i Person 1 04.01.2019 -30.00 a Person 2 04.01.2019 -100.00 800.00 e Person 1
How can I transform the Table so the Balance gets calulated for every row in Power Query like in the following Table?
fTableTransactions Date Debit Credit Balance Category Person 01.01.2019 -10.00 960.00 a Person 1 01.01.2019 -20.00 940.00 b Person 2 01.01.2019 100.00 1040.00 c Person 2 01.01.2019 -40.00 1000.00 d Person 1 02.01.2019 -50.00 950.00 e Person 2 02.01.2019 -40.00 910.00 f Person 2 02.01.2019 50.00 960.00 g Person 1 02.01.2019 -20.00 940.00 h Person 1 02.01.2019 -10.00 930.00 i Person 1 04.01.2019 -30.00 900.00 a Person 2 04.01.2019 -100.00 800.00 e Person 1
I tryed already with adding a -1 Index offet and create a new calculated column.
But the problem is, that I then cannot reference to the previous row in the same
Calculated Balance Column which I am trying to calculate.
Would be thankful for any hint.
Thanks already in advance for any answer or recommendation.
Reagards,
Alain
Solved! Go to Solution.
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.
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.
Hi @Mariusz
First of all thank you for you work on my problem.
I will give it a try an share the outcome asap.
Regards,
Alain
Hi @AlainB
Why Person and category does not impact the balance and why you moved 50 from balance in tbl 1 to credit in tbl 2?
Hi Mariusz
Just an editing mistake in the sample tables.
Just corrected it.
This table is a transaction table from an account (basically how it exports to the .csv).
The Person and Category is later used for relations, filtering and messures.
Regards,
Alain
If there is an M solution, @ImkeF will know what it is. If you can do DAX, you should be able to use EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Thanks for the hint...but as I am not able to reference to the table that I am calculating, I do not see how EARLIER should work in my scenario. Nevertheless I stumbled over another of your articles "For and While Loops in Dax", I wonder if that could be the solution for my scenario...as I have the issue, that I have missing values. And as far as I understand it, with the loop you are calculating "virtual table" and bring the values back in your original table (sorry I am a newbie). But it seems like a littlebit overkill for my scenario.
Today I tried it with LOOKUPVALUE but here I had the same issue that I cannot reference to the Column (previous row) that I am calculating.
Calculated Balance = IF( Table[Index] < 1;
Table[Balance];
LOOKUPVALUE(Table[Balance]; Table[Index]; Table[Index]-1) + Table[Debit] + Table[Credit]
)
Here I am able to reference to another coloumn/value in the previous row (but not to the one that I am creating with "Calculated Balance"...but as soon as I have an empty value in my original blance table I only get the the SUM of [Debit] + [Credit] which is logical.
The other idea that came into my mind a few minutes age....
....why not calcualating for every row the complete [Credit] + [Debit] and always calculate back all previous Transactions? Not shure if this is possible?! :-S
If non of these solutions will work, I think I have to do the workaround over excel, which is not my prefered way.
Hope this gives you an idea what I have tried already.
Regards,
Alain
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.