Unpivoting using QUERY function is not fetching the expected result

useroLwi1 0
Viewed 21

I am currently working on a dataset that includes several columns, mostly the dates. What I am trying to achieve is - unpivot all the date columns to use for my subsequent calculations. I use the following formula to unpivot: =ARRAYFORMULA(SPLIT(FLATTEN(Data!A2:A&"|"&Data!D1:AG1&"|"&Data!D2:AG),"|"))

Even though this returns the expected result, when I try to nest this within a Query function, it does not work correctly. This is how I applied the formula - QUERY(ARRAYFORMULA(SPLIT(FLATTEN(Data!A2:A&"|"&Data!D1:AG1&"|"&Data!D2:AG),"|")),"Select * WHERE Col3 IS NOT NULL")

PS: When I change the data range to say, A2:A100, it gives me the correct result. However, it does not help since lot of new data would get added and I want the formula to be dynamic. Here's the link to the sample sheet - https://docs.google.com/spreadsheets/d/1dgFY5mT9nUJtFefjAros-XpWXRMFtxEf8Fqrv82N5Ys/edit#gid=1813140523

Any help/suggestions would be highly appreciated

google-sheets-formula google-sheets
1 Answers

Not sure where you got your SPLIT(FLATTEN technique, but you have to include both the 3rd and 4th parameters of the split function as FALSE or 0. so in your case it would be:

=ARRAYFORMULA(SPLIT(FLATTEN(Data!A2:A&"|"&Data!D1:AG1&"|"&Data!D2:AG),"|",0,0))

If you do that you'll find your query works.

  • Also note that the way you have it it's not really working. If you look all the way down in column 1 you'll find a bunch of dates formatted to look like integers.
MattKing0
Related