Creating a custom column that calculates the previous row value by previous hour, date, & store

Hi  Anonymous 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjcwUNJRMjTQNzbUNzIwMgJxjKwMDBQcfUFMpVgd7GoQSkxxqUEYY2SAS40pXA1EiSVe55jiVIOwCpcSJE/htMoYxVexAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Date = _t, #"Order Create Hour(UTC)" = _t, #"Total Consumed(w/o Cancellations)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}, {"Date", type date}, {"Order Create Hour(UTC)", type time}, {"Total Consumed(w/o Cancellations)", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Store", Order.Ascending}, {"Order Create Hour(UTC)", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Store", "Date"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Order Create Hour(UTC)", "Total Consumed(w/o Cancellations)", "Index"}, {"Order Create Hour(UTC)", "Total Consumed(w/o Cancellations)", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each List.Min(Table.SelectRows(#"Expanded Data",(x)=>x[Store]=[Store] and x[Date]=[Date] and x[Index]=[Index]-1)[#"Total Consumed(w/o Cancellations)"])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Orders dropped by hour", each if [Index]=1 then [#"Total Consumed(w/o Cancellations)"] else if [#"Total Consumed(w/o Cancellations)"]-[Custom]>0 then [#"Total Consumed(w/o Cancellations)"]-[Custom] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.