I'm using the following Lambda function I found online to un-pivot a table where the first column of data are dates. I would like to do this using a formula and not power query. The Lambda function seems to convert the dates to text uses textjoin & textsplit functions.
How do I modify the formula such that the array spills the first column in date format as it was originally?
How do I make the resulting un-pivoted array into a table so that I can use pivot tables?
I have included a sample file and the Lambda formula is as follows:
=LAMBDA(array,HSTACK(TEXTSPLIT(TEXTJOIN(".",1,DROP(TAKE(array,,1)&"-"&TAKE(array,1),1,1)),"-","."),TOCOL(DROP(array,1,1))))
Thanks.......
Dynamic array formulas do not work in tables, though you don't really need a table to use normal pivot tables.
You could simply amend your output formula to use something like:
=LET(unpivot,UP(A4:E9),CHOOSE({1,2,3},INDEX(unpivot,,1)+0,INDEX(unpivot,,2),INDEX(unpivot,,3)))
