So nice, I made a custom function with that stuff ! It allows to select the whole range and set the number of columns to keep on the left side :-). =UNPIVOT(DATA,NbColumnFixed). The formula is rather complex using by BYROW, OFFESET,COLUMNS,ROWS... But works fine. It keeps the headers and just add 1 header for the column name and 1 for the values.
Thank you! I was actually looking for example to unpivot the data with google query, but you showed how to do it with flatten. Still looking for example for unpivot...
Thanks for this video, I am trying to adapt this to one of my projects - a personal finance budget/expense tracker - where i set it up as a table and i need to UNPIVOT the data so that i can *then* use that to create some dashboards. If I expect that data will continue to be inserted in the initial table horizontally (ie. in your case around the 2:18 mark for the purple and blue boxes) how can i automate the UNPIVOT function such that it will capture that data to account for this change? Because i understand you can set up the =ArrayFormula(...) with an exact reference but how do you account for future data with hundreds of entries? Side note: i see how that can be helpful with the =QUERY function when you take into account all the data in the column but not anything that is blank at 4:29
Oh how life has changed since FLATTEN was discovered! If only you knew the lore behind the magic!
GENIUS!!! Please don't judge me for being so excited about a g-sheet. I don't think I have been this happy in months
Don't worry about it, I nerd out all the time about cool Sheets things
So nice, I made a custom function with that stuff ! It allows to select the whole range and set the number of columns to keep on the left side :-). =UNPIVOT(DATA,NbColumnFixed). The formula is rather complex using by BYROW, OFFESET,COLUMNS,ROWS... But works fine. It keeps the headers and just add 1 header for the column name and 1 for the values.
Awesome work!
Thank you! I was actually looking for example to unpivot the data with google query, but you showed how to do it with flatten. Still looking for example for unpivot...
Glad it helped! There's a new couple of functions called TOCOL and TOROW that can be used to 'unpivot'. I might make a video about it one day
Thanks for this video, I am trying to adapt this to one of my projects - a personal finance budget/expense tracker - where i set it up as a table and i need to UNPIVOT the data so that i can *then* use that to create some dashboards.
If I expect that data will continue to be inserted in the initial table horizontally (ie. in your case around the 2:18 mark for the purple and blue boxes) how can i automate the UNPIVOT function such that it will capture that data to account for this change? Because i understand you can set up the =ArrayFormula(...) with an exact reference but how do you account for future data with hundreds of entries? Side note: i see how that can be helpful with the =QUERY function when you take into account all the data in the column but not anything that is blank at 4:29
Absolutely. In my video at 2:18, you could change the E4:P7 to E4:7. This will select the entire rows even after more data is added.
Super useful, thanks!
You're absolutely welcome, Mario!
Great sir 👍🏻👍🏻👍🏻👍🏻
Thank you!
Nice nice nice🎉
this is HOT STUFF!!
Query has pivot built in. Why not use that?
Check out 5:45
Great f formula