Forum Discussion
Transform multiple date ranges into continuous dates list through Dynamic Array
- Dec 31, 2024
=DROP(REDUCE("",SEQUENCE(ROWS(Input)),LAMBDA(u,v,
LET(
_days,INDEX(Input,v,2)-INDEX(Input,v,1)+1,
_seq,SEQUENCE(_days,1,INDEX(Input,v,1)),
_amt,EXPAND(INDEX(Input,v,3)/_days,_days,1,INDEX(Input,v,3)/_days),
VSTACK(u,HSTACK(_seq,_amt))))),1)This returns the expected result in my Excel for the web sheet. The name of the input table is "Input".
Can you share your M-code that achieves the same result?
I offer a recursive solution. I enjoy the challenge of coming up with recursive solutions because there's not a lot technical documentation from Microsoft so it often feels like the wild west of Excel. The limit of this function is noted below. The limit can be increased even more if I nest functions and eliminate parameters but I can't forsee unpackings needing to be done with tables encompassing more than a few thousand rows (I could be wrong!).
//Unpack a table by recursion. Function presumes table
//includes col1: Start Date, col2: End Date, col3: Amount.
//Recursion limit: 2,047 rows in packed table.
UnPackλ =
LAMBDA(table,
LET(
next,TAKE(table,1),
start,TAKE(next,,1),
diff,1+INDEX(next,,2)-start,
dates,SEQUENCE(@diff,,@start),
amount,TAKE(next,,-1)/SEQUENCE(@diff,,@diff,0),
resize,VSTACK(DROP(table,1),HSTACK(dates,amount)),
IF(diff<0,DROP(table,,-1),UnPackλ(resize))))
Thank you, Patrick. It worked perfectly.