Kusto to construct a new table based on values from another table

Question

Need help in Kusto to construct a new table based on values from another table: I have a table like this:

month_end_date  value
2023-04-30      0
2023-05-31      1
2023-06-30      0

I need to construct dates like this:

print date_part=range(todatetime('2023-05-28'), todatetime('2023-07-03), 1d)
| mvexpand date_part

and fill out values from my previous table with these new date ranges. meaning if the date is >=2023-06-30 or the latest date, fill out with the value in value column which is 0, and if it is between 2023-06-30 and 2023-05-31, with respected value in 2023-05-31 which is 1, and if less than 2023-05-31, with respected value for 2023-04-30 which is 0. construction of date_part could be different like just using extend and add dates based on the range specified, whatever helps achieve better result.

Answer 1

I have reproduced in my environment and got expected results as below:

Below is the KQL query which worked for me:

let originalTable = datatable(month_end_date: datetime, value: dynamic)
[
    datetime(2023-04-30), 0,
    datetime(2023-05-31), 1,
    datetime(2023-06-30), 0
];
let dateRange=range x from todatetime('2023-05-28') to todatetime('2023-07-03') step 1d;
let r=originalTable
| join kind=fullouter (
    dateRange
) on $left.month_end_date == $right.x
| extend date1=iff(isnull(month_end_date),x,month_end_date)
|order by date1 asc 
|project-away month_end_date,x
| sort by date1 asc
| extend rn=row_number();
r
| project date1,value
| summarize c = make_list_with_nulls(value)
| extend d = series_fill_forward(c)
| mv-expand d = d 
|project-away c
|extend valuess= toint(d)
| extend new_column = 0
| sort by new_column asc
| extend rn=row_number()
| join kind=inner r on rn
|project-away rn, rn1,value,new_column,d

Output:

1st part

2nd part

Fiddle.

Try to follow to follow above query to get desired result as I have got.