match column and subtract value from previous day

[Simplyjustdavid]

Hello all,

Kinda been racking my brain and my google search with this one.

We have a csv datadump into power query, each row in that dump has date/unit #/ accumulated total. 

Each day sales accumulated upward:

  Date             Loc       Unit#     NA             NA            NA                   Sales

6/28/2021	551109	76302	0	0	0	5230
6/29/2021	551109	76302	0	0	0	5295
2/5/2022	551109	76303	0	0	0	3790
2/6/2022	551109	76303	0	0	0	3790

is theres a way to create a custom column that match the unit # (so that it doesnt subtract once the data goes to a new unit) and substract the previous day sales?

[BA_Pete · Accepted solution]

Hi @Simplyjustdavid ,

Sort your query in PQ by [Unit#] ascending, then by [Date] ascending.

Add an index column that starts from 0 and call it "index0".

Add another index column that starts from 1 and call it "index1".

Go to the Home tab > Merge Queries and left outer merge the table on itself on [Unit#][index0] (top window) = [Unit#][index1] (bottom window).

Expand the sales column from the resulting nested table column and, voila, previous days' sales.

Paste this over the default code in Advanced Editor to see the steps in action:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNM1slDSUTI1NTQ0sAQyzM2MDYxAAkbGBkqxOkjKLLErszSFKTPSNQAiU3RlxkDa2NzSAFWZGW5lsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Loc = _t, Unit = _t, Sales = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Loc", Int64.Type}, {"Unit", Int64.Type}, {"Sales", Int64.Type}}),
    sortUnitDate = Table.Sort(chgTypes,{{"Unit", Order.Ascending}, {"Date", Order.Ascending}}),
    addIndex0 = Table.AddIndexColumn(sortUnitDate, "Index0", 0, 1, Int64.Type),
    addIndex1 = Table.AddIndexColumn(addIndex0, "Index1", 1, 1, Int64.Type),
    mergeOnSelf = Table.NestedJoin(addIndex1, {"Unit", "Index0"}, addIndex1, {"Unit", "Index1"}, "addIndex1", JoinKind.LeftOuter),
    expandSalesCol = Table.ExpandTableColumn(mergeOnSelf, "addIndex1", {"Sales"}, {"prevSales"})
in
    expandSalesCol

Pete