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.
Hello Everybody,
I need your help solving a problem.
I am trying to calculate a new value of a row using the previous value of the row.
My attempts using Dax ended all in circular dependancys.
My hope is, that what I want to do is possible in Power Query using M.
I constructed following Query/Table:
BOZ1 is some historical data, this column will not change with refresh and no new values will be added.
In order to calculate the NewValue from 24/11/20 and onward I need to take the 144 as a starting point and subtract the value in VOZ: 144 - 0,46 = 143,54. This will be my value for 24/11/20.
The Value for 25/11/20 is 143,54-0 = 143,54 and so on.
I am not sure how to proceed.
With the help of Google I figured out two possible solution:
1. Write a loop as a custom function and invoke it to add a new column
or
2. Use this solution: https://stackoverflow.com/questions/57460424/iterative-calculation-in-power-query
Both solutions are high above my current skillset.
I hope somebody can give me some assistance, as this problem is really keeping me up at night.
Best Regards
Rain
Solved! Go to Solution.
Hi @Anonymous ,
please paste the following code into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRMzTUMzIwMlDSUTKzNAaSQGSgZ2JmCARKsTpAJcYoSoyApKGJCUhhbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datum = _t, Index = _t, BOZ1 = _t, #"VOZ Sys1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type date}, {"BOZ1", Int64.Type}, {"VOZ Sys1", type number}, {"Index", Int64.Type}}),
BufferedStart = Table.Buffer( Table.Sort(#"Changed Type",{{"Index", Order.Ascending}}) ),
Initial = BufferedStart{0},
ListGenerate = List.Generate( ()=>
[Result = Initial[BOZ1], Counter = 0],
each [Counter] < Table.RowCount(BufferedStart),
each [
Result = [Result] - BufferedStart[VOZ Sys1]{Counter},
Counter = [Counter] + 1
],
each [Result]
),
Custom1 = Table.FromColumns(Table.ToColumns(BufferedStart) & {ListGenerate} , Table.ColumnNames(BufferedStart) & {"Result"})
in
Custom1
To adapt it to your solution, you have to replace the code in the first step (Source) to a reference to oyur table.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @Anonymous
you can apply a Table.Distinct on your Index-column and then add this formula to a custom column
try RemovedDuplicates{[Index= [Index]-1]}[BOZ1] - [VOZ Sys1] otherwise null
Here the complete example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRMzTUMzIwMlDSUTKzNAaSQGSgY2JmCARKsTpAJcYoSoyApKGJCUhhbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datum = _t, Index = _t, BOZ1 = _t, #"VOZ Sys1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type date}, {"BOZ1", Int64.Type}, {"VOZ Sys1", type number}, {"Index", Int64.Type}}, "de-DE"),
RemovedDuplicates = Table.Buffer(Table.Distinct(#"Changed Type", {"Index"})),
#"Added Custom" = Table.AddColumn(RemovedDuplicates, "Result", each try RemovedDuplicates{[Index= [Index]-1]}[BOZ1] - [VOZ Sys1] otherwise null )
in
#"Added Custom"
The performance of this should be also very good
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Anonymous ,
please paste the following code into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRMzTUMzIwMlDSUTKzNAaSQGSgZ2JmCARKsTpAJcYoSoyApKGJCUhhbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datum = _t, Index = _t, BOZ1 = _t, #"VOZ Sys1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type date}, {"BOZ1", Int64.Type}, {"VOZ Sys1", type number}, {"Index", Int64.Type}}),
BufferedStart = Table.Buffer( Table.Sort(#"Changed Type",{{"Index", Order.Ascending}}) ),
Initial = BufferedStart{0},
ListGenerate = List.Generate( ()=>
[Result = Initial[BOZ1], Counter = 0],
each [Counter] < Table.RowCount(BufferedStart),
each [
Result = [Result] - BufferedStart[VOZ Sys1]{Counter},
Counter = [Counter] + 1
],
each [Result]
),
Custom1 = Table.FromColumns(Table.ToColumns(BufferedStart) & {ListGenerate} , Table.ColumnNames(BufferedStart) & {"Result"})
in
Custom1
To adapt it to your solution, you have to replace the code in the first step (Source) to a reference to oyur table.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke,
thank you very much, your solution worked like a charm!
I still have to understand the solution as a whole, but beeing able to implement it is a good starting point.
Best Regards
Vanessa
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.