Forum Discussion
Optimize performance at Un-Cumulate calculation
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})
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"
wdx223_Daniel's solution is brilliant but somewhat difficult to understand and adapt.
Here's an approach inspired by them but (hopefully) a bit more transparent:
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 Rows" = Table.Sort(#"Changed Type",{{"Jahr", Order.Ascending}, {"Datum", Order.Ascending}}),
RecordList = Table.ToRecords(#"Sorted Rows"),
ListGen =
List.Generate(
() => [curr = RecordList{0}, prev = RecordList{0}, u = RecordList{0}[Datum], i = 0],
each [i] < List.Count(RecordList),
each [
curr = RecordList{i},
prev = RecordList{i-1},
u = if curr[Kennzahl] = "Menge"
and curr[Jahr] = prev[Jahr]
and curr[Region] = prev[Region]
then curr[Datum] - prev[Datum]
else curr[Datum],
i = [i] + 1
],
each [curr] & [DatumUncum = [u]]
),
RecordsToTable = Table.FromRecords(ListGen),
#"Changed Type1" = Table.TransformColumnTypes(RecordsToTable,{{"ID", Int64.Type}, {"Jahr", Int64.Type}, {"Kennzahl", type text}, {"Region", type text}, {"Datum", Int64.Type}, {"DatumUncum", Int64.Type}})
in
#"Changed Type1"
Thanks a lot! Actually wdx223_Daniel s solution did not work properly since i got a stack overflow error which i was not able to resolve. Further what is missing in both solutions but was no big challange to add myself was the fact that there are multiple items and therefor i need to un-cummulate the amount per item and year!, which means i cannot simply sort the list and then subtract the prev amount from the line underneath.
What i actually did -
- I sorted per Region / Kennzahl & Date
i created an GroupIndex (per Region/Kennzahl/YEAR AND Item) - since the amount is always being cummulated per Year and Item which means first entry in Jan of any year is the absolute amount - the following entry of same item in Feb is (KummFeb - JanAmount) etc.
- I created a simple record index column
- I created a JoinIndex Col which is Conditional (If GroupIndex = 1 then 1 else [GroupIndex}{[Index]-1})
- THen i can simply make NestedJoin (Left.Outer) by joining GroupIndex & JoinIndex
- Finally i have the corresponding KummAmount to subtract from in one line to calc. Abs. Amount
In this form i have an Absolute Amount per Region/Kennzahl/Year AND ITEM - this is important since prev. solutions (as i understood) where based on the idea like there would only be on single Cummulative Amount but i have to separate between years and items in addition to that.
I will copy the full code next week if anybody is interested !
Both of the solutions I gave are easily extendable to grouping on additional columns.
In the Index0/Index1 version, you include these additional columns to match on in the self-merge. In the List.Generate version, you add additional "and curr[col] = prev[col]" conditions. For both, you do need to make sure that your table is sorted before doing the other stuff.