I have a Date column and value column, I need to create a new column (Expected Date) with Date values w.r.t value column. The screenshot attached here has some sample data. Expected Date column will take the date when there is a value in the value column, it has to take same date value till the next value got updated. I am trying to achieve this with prev(), but its giving result shown (4th column) in the screenshot. I have tried fill_forward() function it didn't work as this is the Date values.
I have reproduced in my environment and below are my expected results:
KQL Query which worked for me:
let ST = datatable (Date: datetime, Number: int) [
datetime(2023-01-28 00:00:00), 2,
datetime(2023-01-28 00:00:01), int(null),
datetime(2023-01-28 00:00:02), int(null),
datetime(2023-01-28 00:00:03), int(null),
datetime(2023-01-28 00:00:04), int(null),
datetime(2023-01-28 00:00:05), 3,
datetime(2023-01-28 00:00:06), int(null),
datetime(2023-01-28 00:00:07), int(null),
datetime(2023-01-28 00:00:08), int(null),
datetime(2023-01-28 00:00:09), 4,
datetime(2023-01-28 00:00:10), int(null),
datetime(2023-01-28 00:00:11), int(null)
]
|extend new_column2=0
| sort by new_column2 asc
|extend rn=row_number();
let x= ST
| extend new_column = 0
| sort by new_column asc
|extend d= iff(Number != int(null),Date,datetime(null))
|summarize c = make_list_with_nulls(d)
| mv-apply with_itemindex=idx ExpDate = c on
(
extend fillIndex = iff(isnull(ExpDate), 0, idx)
| extend fillIndex = row_cumsum(fillIndex, fillIndex != 0)
| extend ExpDate = c[fillIndex]
| summarize ExpDate = make_list_with_nulls(ExpDate)
)
|mv-expand ExpDate
| extend new_column2 = 0
| sort by new_column2 desc
|project-away c
| sort by new_column2 asc
|extend rn=row_number();
let New =
ST
| join kind=inner (x) on rn;
New
|project-away new_column2,new_column21,rn,rn1
Output: