Forum Discussion
Optimize performance at Un-Cumulate calculation
- 4 years ago
assume your data is sorted by Datum from smallest to largest. then try this code
=Table.FromRecords(List.Accumulate(Table.ToRecords(#"Removed Duplicates"),{{},0},(x,y)=>{x{0}&{y&[Wert=if y[Kennzahl] = "Menge" then y[KummWert] -x{1} else y[KummWert]]},if y[Kennzahl] = "Menge" then y[KummWert] else x{1}}){0})
- 4 years ago
I forgot to include the final steps of taking the difference.
Here's what the result looks like (highlighted columns are new, non-highlighted are starting data):
Here's the full code for this example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJV0lEyBGLf1Lz0VCDtCOKbKsXqRCsZGmGRM4LImVqA2EhyTkBsYgCWMzbBImcEkTOxxCJnCJSLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Jahr = _t, Kennzahl = _t, Region = _t, Datum = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Jahr", Int64.Type}, {"Kennzahl", type text}, {"Region", type text}, {"Datum", Int64.Type}}), #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Jahr", Order.Ascending}, {"Region", Order.Ascending}, {"Datum", Order.Ascending}}), #"Added Index0" = Table.AddIndexColumn(#"Sorted Rows1", "Index0", 0, 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index0", "Index1", 1, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0", "Jahr", "Kennzahl", "Region"}, #"Added Index1", {"Index1", "Jahr", "Kennzahl", "Region"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Datum"}, {"PrevDatum"}), #"Sorted Rows2" = Table.Sort(#"Expanded Added Index",{{"Jahr", Order.Ascending}, {"Region", Order.Ascending}, {"Datum", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows2", "UncumDatum", each [Datum] - (if [PrevDatum] = null then 0 else [PrevDatum]), Int64.Type) in #"Added Custom"
assume your data is sorted by Datum from smallest to largest. then try this code
=Table.FromRecords(List.Accumulate(Table.ToRecords(#"Removed Duplicates"),{{},0},(x,y)=>{x{0}&{y&[Wert=if y[Kennzahl] = "Menge" then y[KummWert] -x{1} else y[KummWert]]},if y[Kennzahl] = "Menge" then y[KummWert] else x{1}}){0})
- padinator4 years agoHelper I
hei wdx223_Daniel sorry i did not yet want to accept your solution since it does not work unfortunately 😕
First of all , i get a stack overflow as i run the function and in addition I think there is one important detail you missed which is the fact that i do not simply need to un-cummulate row by row but still have to differentiate between the particular Item numbers as well!!
So simply going down and using the sum of the prev is not working since i need to sort by item as well and somehow realize when i enter a new item - then again the first row should be considered KummAmount = Amount and then from the Second row of an item the calculation should start again !
I hope I kinda explained the problem good enough. If there is still something unclear let me know! I will try to find a fix for this issue in the meantime myself, but if you would have some input i could try out it would be highly appreciated !
- padinator4 years agoHelper I
hei i am still trying to make your function work but i still get the Stack Overflow error although i grouped, and indexed my table now. What i do not understand in your function ==> what exactly does your "Seed" of "{{},0}" mean and how was it ment to work ? Could you be so kind to just briefly explain in short words so that i might start understanding the underlying idea? Thanks !