Unpivot in Google Sheets with FLATTEN: Column pairs, auto expand++
HTML-код
- Опубликовано: 16 июл 2024
- Unpivoting/reverse pivoting was a game-changer when Excel introduced it with Power Query, Google Sheets can achieve the same result. Using a combination of two key functions FLATTEN & SPLIT you can unpivot a cross tab dataset into a tabular data set (a line by line transactional one). This could make your spreadsheet into a complex queryable data model. This video tackles 5 scenarios, many of which I have used hundreds of times over the last decade in Excel/Gsheets, with enhancements using the QUERY & TEXTJOIN functions
Example files can be found here: www.xlconsulting-asia.com/youtube-files
00:00 - Introduction
01:50 - Simple Unpivot
03:46 - Unpivot checkboxes & add named headers
07:04 - Unpivot some but not all cols with TEXTJOIN
09:26 - Unpivot with auto-expanding new rows & cols
11:02 - Unpivot column pairs (e.g. US qty/price, Canda Qty/price etc.)
Other functions explored in more detail:
QUERY: • QUERY Complete guide: ...
TEXTJOIN: • TEXTJOIN, CONCAT &, TE...
SPLIT/ARRAYFORMULA: • Google Sheets: Dynamic... 
Хобби
A lot of my work involves unpivoting wide tables. A few years ago I was using a Google Apps script I found (creatively named _UNPIVOT)_ which worked well enough but had limited flexibility and would occasionally bug out and fail to load. About 2 years ago someone on the Google product forums showed me an undocumented function called _FLATTEN_ and I was instantly hooked. A little while later _FLATTEN_ was canonized and I no longer had a secret weapon. 😂 Anyway, speaking as someone who's been using it for a long time, I can say that this is the best tutorial of the function that I've seen so far, and I wish it existed three years ago.
Awwww that’s so sweet!! Thanks so much. I also used it once or twice when it was a secret weapon as I love using unpivot in power query. Spread the love! Great comment to read 😃
I have spent about a day in search of how to unpivot data, found several videos and formulas all way too complicated, this video puts it simple, clear and straight to the point. Thank you David for this, is the best video I found on unpivoting data.
Wow! That’s so nice to read, really happy that it helped you! Don’t feel shy sharing it around 😃
You've solved a life-long issue of mine in 5 minutes! thank you :)
That’s amazing! Thanks for the kind words, in excel the equivalent is attainable via power query
That's EXACTLY what I needed for working with multiple datasets that have different pivoting.
Yay glad you like it!
Thank you so much for this video!!! I knew it had to be possible somehow, spent hours looking for a solution and this worked magically!
Yay! That’s a really lovely comment to read, glad it worked out. Happy new year 😃
Precisava muito desse conteúdo, ninguém no BR produziu.
Me ajudou muito, irmão. Sucesso pra você! 🚀🔥
Thanks so much! Glad it was helpful!
An awesome lesson! Thanks for showing these techniques in Google Sheets. Thumbs up!!
Glad you like it! I had to make this for a client job so I learned how then made a video!
Thank you so much!!! You are a genious!! (Muchas gracias, saludos desde Argentina!!)
Es siempre bien de recibir mensajes como estas. Gracias Marcos!
This is super useful!!!
Glad you like it!
Thank you
No worries! Glad you like it!
top!! merci
Pas de quoi, je suis contempt que vous l’avait aimé!
super awesome
Thx
Yay
was wondering if there was any way that you could bypass those columns you had to delete on the column pairs section. Im trying to automate some sheets and having to delete those non useful ones causes me issues. this has been so helpful!
Glad you find it helpful, the process in this video is the most efficient way I have found, but you can create a named function to do it with less code. If you have Excel data though, then you can also use Power Query to get to a no code solution.
Perfetc. How Do that Vertical to Horizontal? Opposite that you did.
It its jut one line TOROW should work, or to just flip, TRANSPOSE
Great Video and explaining , i knew this methode but i wanted to exclude empty rows with no data ,now i know it with query , perfect .... just one Problem what to do with empty Celles ?
Hi the empty rows would be excluded with query but the empty cells would be included as separators which is usually what is required otherwise the columns don’t stack up properly. If you don’t enter the last argument in the SPLIT formula you can do different things with empty cells though.
Great video David but a bit fast for me in places! Is it possible to have access to the file please?
Yes sure! Send me an email to david@xlconsulting-asia.com
Thanks a lot for the video! Unfortunately I have a problem, I have exactly the same data and cells as the sheet on the video and yet when I write the formula I have a blank cell... no error or formatting problem
Hi Oni. Sorry to hear, unfortunately its hard to understand what is going on from a short text explanation. If you ask in one of the G Sheets forums you can send pictures and files alongside posts which may be more help than I can give - apologies
Hi, very interesting but didn´t help me. I am looking to unpivot but to make as much lines as the value (in numbers) appears in the data. Example: in your first table, 736 lines for "california Jack Jan", 21 lines for California Jack Feb", and so on. Is that possible?
Hmmm even with Excel’s power query that is very difficult & I don’t know how I would approach it with google sheets
This has been extremely helpful. I have been trying to figure out how to Unpivot data from the work assignment calendar that my boss puts out every month in a poorly formatted table so I can do things like import the data into google calendar. Ive mostly got it figured out but sometimes he puts more than one name in a cell, and I can't figure out how to separate them into new rows instead of columns. So here's my function ... =QUERY(ARRAYFORMULA(SPLIT(FLATTEN(B3:AE22&"|"&B2:AE2&"|"&A3:A22),"|",TRUE,FALSE)),"Select * where Col1 is not null order by Col1 asc, Col2 asc")
Any ideas how i can split cells that contain more than one entry to create more rows instead of more columns? for example I want
BL;CB 44889 PTO
to appear as
BL 44889 PTO
CB 44889 PTO
Also that number '44889' is a date. why is it formatted as a date in my original array, but not in the output?
Hiya thanks for the feedback I’m glad you like it, with the date, excel/sheets store dates as numbers so you would need to use =TEXT(value,”dd-mmm-yy) to take it as a date
I don’t think your other request is possible without having the source data explicitly shown is possible, it would probably need an extra step before the query to repeat the 44889 PTO in both rows