Forum Discussion
Anonymous
2 years agoNot applicable
Creating a custom column that calculates the previous row value by previous hour, date, & store
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 ...
AlienSx
2 years agoSuper User
hi, Anonymous
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])- Anonymous2 years agoNot applicable
Is this VBA? Unfortunately I need it for powerquery.