azureazure-data-explorerkqlkusto-exploreradx

How to fill empty values in a column to the latest record value in KUSTO


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. enter image description here


Solution

  • 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:

    enter image description here

    Fiddle.