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

[tonyk86]

Hi,

   I'm really struggling with this. I'm trying to create a custom column that calculate the previous row Total Consumed (w/o Cancellations) column. The Total Consumed (w/o Cancellations) column is just a running cummulative total of orders by hour (within the UTC date) from the datasource.

What I want is to calculate the difference between the the prevous row values as this would be the actual number of orders dropped to that specific location on that hour for that particular store. Obviously, I wouldn't bother doing this if the datasource had this informaiton but it does not so I need to find a reliable way. I've first created two index columns and subtracted the difference but this wasn't reliable as it produced some negative numbers or incorrect numbers.

Also, because of the way of the datasource, not every store would have all 24 hour for a date. So you might see a 8 orders consumed at 12:00 AM but then the store may not have an order until 5:00 AM so it's not consistent.  ANy help would be appreciated on this. Thank you!

Here is what I want:

[v-xinruzhu-msft]

Hi  @tonyk86 

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.

[AlienSx]

hi, @tonyk86 

    s = your_table,
    f = (tbl as table) =>
        [rows = List.Buffer(Table.ToRecords(tbl)),
        rt = List.Generate(
            () => [i = 0, r = rows{0}, rr = Record.Field(r, "Total Consumed (w/o Cancellations)")],
            (x) => rows{x[i]}? <> null, 
            (x) => 
                [i = x[i] + 1,
                r = rows{i},
                rr =  List.Max(
                    {0, 
                    Record.Field(r, "Total Consumed (w/o Cancellations)") - 
                    Record.Field(x[r], "Total Consumed (w/o Cancellations)")})],
            (x) => x[r] & [Orders Dropped by Hour = x[rr]]
        ),
        z = Table.FromRecords(rt)][z],
    g = Table.Group(s, {"Store", "Date"}, {{"t", each f(Table.Sort(_, "Order Create Hour (UTC)"))}}),
    fin = Table.Combine(g[t])

[tonyk86]

Is this VBA? Unfortunately I need it for powerquery.

[ronrsnfld]

If your logic is to calculate the difference between two rows, given a matching "Store-Ke" and "Date", you are going to have at least one negative value given the data you show in your second picture. If negative values cannot exist, you may need to rethink your logic.

[tonyk86]

I think a logic can be placed if there is negative numbers. If negative, then bring back 0 as technically 0 orders would have been orders dropped to that store for that hour and date. However, trying to come up with the entire logic is where i'm struggling. 

[ronrsnfld]

Given your stated logic, and starting from the data in your second screenshot, the following code seems to do what you say you want:

Documentation is within the code and comments

let

//change next line to refer to your last table
    Source = Excel.CurrentWorkbook(){[Name="Table23"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Store- Ke", Int64.Type}, {"Date", type date}, {"Order Create Hour (UTC)", type time}, 
        {"Total Consumed (w/o Cancellations)", Int64.Type}}),

//Group by Store-Ke and Date
// you can add a sort if necessary
//  then aggregate to create Orders Dropped column according to your state logic
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store- Ke", "Date"}, {
        {"Orders Dropped by Hour", (t)=>
            let 

            //To sequential rows, we add a column which is the same column shifted up one
            // the logic ensures that the first row in each subgroup will be set to the original value
            // and that no value can be less than zero (0)
                #"Shift Consume" = Table.FromColumns(
                    Table.ToColumns(t) &
                    {{null} & List.RemoveLastN(t[#"Total Consumed (w/o Cancellations)"],1)},
                    {"Store-Ke", "Date","Order Create Hour (UTC)","Total Consumed (w/o Cancellations)", "Shifted Consumed"}),
                #"Add Orders Dropped" = Table.AddColumn(
                    #"Shift Consume","Orders Dropped by Hour", each 
                        List.Max({0, 
                        ([#"Total Consumed (w/o Cancellations)"] - [Shifted Consumed]) ?? [#"Total Consumed (w/o Cancellations)"]}) ,
                         Int64.Type )
            in  
                #"Add Orders Dropped",
                type table[#"Order Create Hour (UTC)"=time, #"Total Consumed (w/o Cancellations)"=Int64.Type, 
                            Orders Dropped by Hour=Int64.Type]
                }
    }),
    #"Expanded Orders Dropped by Hour" = Table.ExpandTableColumn(#"Grouped Rows", "Orders Dropped by Hour", 
        {"Order Create Hour (UTC)", "Total Consumed (w/o Cancellations)", "Orders Dropped by Hour"})
in
    #"Expanded Orders Dropped by Hour"

[tonyk86]

Hi @ronrsnfld ! Thanks. I"ve tried your method but noticed that some of the numbers are off. It seems that as we get towards the later hours (for example 5 PM), the running difference (orders drop by hour) would be off.  The screenshot below shows 74 orders were dropped at 5 PM. However, the total consumed at 4 PM was 117 and at 5 PM, it was 129. This would mean 5 PM would have had 12 orders dropped to that store. I checked my sort and it looks right. Any idea why this would be?

[ronrosenfeld]

Maybe you sorted the data after instead of before the calculations were done. If that's not the problem, I'd need to see the actual data that is producing that result, not a screenshot.

[tonyk86]

Thanks! Sorry for the late reply. I"m going to try to post sample. I've tried multiples times and couldn't figure it out. 

[ronrsnfld]

---

@tonyk86 wrote:

Thanks! Sorry for the late reply. I"m going to try to post sample. I've tried multiples times and couldn't figure it out. 

---

Take a look here