Log In
Register
Home
Dashboards
August 20, 2022
Offline
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.......
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
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)))
Answers Post
1 Guest(s)
