match column and subtract value from previous day

Question

Hello all,&nbsp;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.&nbsp;Each day sales accumulated upward:&nbsp; Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Loc&nbsp; &nbsp; &nbsp; &nbsp;Unit#&nbsp; &nbsp; &nbsp;NA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales6/28/20215511097630200052306/29/20215511097630200052952/5/20225511097630300037902/6/2022551109763030003790&nbsp;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)&nbsp;and substract the previous day sales?&nbsp;

ba_pete · Accepted Answer

Hi&nbsp;Simplyjustdavid&nbsp;,
&nbsp;
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 &gt; 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.
&nbsp;
Paste this over the default code in Advanced Editor to see the steps in action:
&nbsp;
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
&nbsp;
&nbsp;

&nbsp;
Pete

Forum Discussion

match column and subtract value from previous day

1 Reply

Related content

Subtraction calulation

Subtract two columns

Subtract one month from date column

DAX Formula for subtracting columns?

Subtract previous days using M-Query

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