Forum Discussion
Solved
Optimize performance at Un-Cumulate calculation
Hello, i have the following issue trying to optimize at the moment. I have a really huge amount of data with thousands of records where unfortunately the dataset i receive is only showing the cummul...
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"
Calling functions row by row tends to be pretty inefficient.
You can speed things up immensely by doing things with joins instead. Sort your data appropriately, add two index columns, and do a self-merge to grab the prior value.
Here's a sample query to demonstrate the concept:
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}})
in
#"Sorted Rows2"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"Thanks! Actually this seems to be the only viable solution! I have unintentionally accepted the other post also as solution but this did not work out! Thanks a lot for your help
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"
