M - Transforming cumulative value (running total) to actual value

Question

A user recieves monthly customer reports with running totals of monthly sales (few tens of thousand lines). The sales are displayed as running total, I'm trying to find a powerquery solution to get the actual sales as (*Required*) output:

╔══════╦═══════╦═════════╦═══════╦════════════╗
║ Year ║ Month ║ StoreID ║ Sales ║ *Required* ║
╠══════╬═══════╬═════════╬═══════╬════════════╣
║ 2017 ║    10 ║       1 ║     5 ║          5 ║
║ 2017 ║    11 ║       1 ║    11 ║          6 ║
║ 2017 ║    12 ║       1 ║    18 ║          7 ║
║ 2017 ║    11 ║       2 ║    10 ║         10 ║
╚══════╩═══════╩═════════╩═══════╩════════════╝

In tSQL I'd do something like

Sales - LAG(Sales,1,0)OVER(Partiton by Year, StoreID Order by Month)

A simular question (but in reverse) was answered, but the proposed solution looks like the equivalent of a Cross Join. I'm new to powerquery (so I may be misunderstanding) but I cant see this being viable as datasets grow every month. Is there a better way to approach this, I cannot find an example in "M"?

Edit: List.Range looks promising

Answer 1

Actually you require values from the previous row on the current row. A common approach is to add 2 Index columns, one starting with 0 and the other starting with 1. Next, merge the table with itself using the Index columns as join columns. After expanding the required columns from the nested tables, you can add a custom column with the required calculation.

let
    Source = Sales,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"StoreID", "Sales"}, {"Previous.StoreID", "Previous.Sales"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Required", each [Sales] - (if [StoreID] = [Previous.StoreID] then [Previous.Sales] else 0), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Previous.StoreID", "Previous.Sales"})
in
    #"Removed Columns"