Calculate change in data - Is there a more efficient alternative to merge?

[tatmaninov]

I am starting with data like this (data_tbl);

And want to end up with data like this (Output);

See here when applying a filter on BU, Dept and Team the Previous Value equals Value from Row below

High level summary is that a batch of data is added at regular intervals, although not always consistent gap between batches of data.  I want to calculate the change in the data compared to the previous batch of data.

The way I do this now is with a Date table to identify the previous date;

let
    Source = Table.Distinct(Table.SelectColumns(data_tbl,"Date")),
    #"Sorted Rows" = Table.Sort(Source,{{"Date", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Date"}, {"Previous Date"})
in
    #"Expanded Added Index1"

Then merge the Previous date to a copy of the data_tbl called Output.  Then use this previous date reference to do another merge to bring through previous data then just add new column to subtract the two

let
    Source = Excel.CurrentWorkbook(){[Name="data_tbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"BU", type text}, {"Dept", type text}, {"Team", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "BU", "Dept", "Team"}, {{"Value", each List.Sum([Value]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Date"}, Dates, {"Date"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Previous Date"}, {"Previous Date"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Query1", {"Previous Date", "BU", "Dept", "Team"}, #"Expanded Query1", {"Date", "BU", "Dept", "Team"}, "Expanded Query1", JoinKind.LeftOuter),
    #"Expanded Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Query1", {"Value"}, {"Previous Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded Query1",null,0,Replacer.ReplaceValue,{"Previous Value"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Value Change", each [Value] - [Previous Value]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Date", Order.Descending}})
in
    #"Sorted Rows"

This works nicely with small amount of data, but when my data starts to get over 100k rows it is unbearably slow.  Is there a more efficient way to achieve similar result?

Link to example file if needed

Example file 

[v-jingzhan-msft]

Hi @tatmaninov 

This is my solution. Not sure if this would be more efficient but you may give it a try!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZY7awMxEIT/y9WG0/ukMm7Sp0piXBhykMIkweD8/ggkXSTv47aw1XzMzs2Ozj6dJj3r2SjjpsP0dP35vOTzNX9e1o/87dR0PpDM821dv/LpLUcdr/c1H2kRQJFi3rpxUXNUMR5FSomlmnH3CB1vl9/vEVqAUg+1eaxSMZ5Alo157yMAYfZU9eQrpPjIHUttUSUWK+Z9FGlFS2JdpFoEGZZqEzXt/j96BydiS/Q8Vo0ZEbXAFWHrToHFiv1ATxyqw2uN4Qe2Otax1KN7AivujRdpBVqri9WJqHb7CWqzT2fRXVs4EetOsCzWugNzRSitSKrfd4DBwu44+IxcdwislZo31tI3opGuYMbPubFkeywPbWKK56q1IMPqSxjFkKWTXG2QUM1qnhsu8EAhHYqex7ahiDnkt4uhhp3ucOM/hj1MK2Rd6Ngdrr6IkkxtyXfh/Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, BU = _t, Dept = _t, Team = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"BU", type text}, {"Dept", type text}, {"Team", type text}, {"Value", Int64.Type}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "BU", "Dept", "Team"}, {{"Value", each List.Sum([Value]), type nullable number}}),
    alldates = Table.Distinct(Table.SelectColumns(#"Grouped Rows", "Date")),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"BU", "Dept", "Team"}, {{"All Data", each Table.NestedJoin(alldates, {"Date"}, _, {"Date"}, "DateTable", JoinKind.FullOuter)}}),
    #"Expanded All Data2" = Table.ExpandTableColumn(#"Grouped Rows1", "All Data", {"Date", "DateTable"}, {"Date", "DateTable"}),
    #"Expanded DateTable" = Table.ExpandTableColumn(#"Expanded All Data2", "DateTable", {"Value"}, {"Value"}),
    #"Grouped Rows2" = Table.Group(#"Expanded DateTable", {"BU", "Dept", "Team"}, {{"All Data", each Table.AddIndexColumn(Table.Sort(_, {"Date", Order.Descending}), "Index", 0, 1)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows2", {"All Data", each let vData =  _ in Table.AddColumn(_, "Previous Date Value", each let vIndex = [Index] in try Text.From(vData[Date]{vIndex+1}) &","& Text.From(vData[Value]{vIndex+1}) otherwise null)}),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"All Data"}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Removed Other Columns", "All Data", {"Date", "BU", "Dept", "Team", "Value", "Previous Date Value"}, {"Date", "BU", "Dept", "Team", "Value", "Previous Date Value"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded All Data", "Previous Date Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Previous Date Value.1", "Previous Date Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Previous Date Value.1", type date}, {"Previous Date Value.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Previous Date Value.1", "Previous Date"}, {"Previous Date Value.2", "Previous Value"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Descending}, {"BU", Order.Ascending}, {"Dept", Order.Ascending}, {"Team", Order.Ascending}, {"Previous Date", Order.Descending}}),
    #"Grouped Rows3" = Table.Group(#"Sorted Rows", {"Date"}, {{"alldata", each Table.FillDown(_, {"Previous Date"})}}),
    #"Expanded alldata" = Table.ExpandTableColumn(#"Grouped Rows3", "alldata", {"BU", "Dept", "Team", "Value", "Previous Date", "Previous Value"}, {"BU", "Dept", "Team", "Value", "Previous Date", "Previous Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded alldata",null,0,Replacer.ReplaceValue,{"Value"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Previous Value"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Value Change", each [Value] - [Previous Value])
in
    #"Added Custom"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!