Unpivot in Google Sheets with FLATTEN: Column pairs, auto expand++

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: Google Sheets' ...  
TEXTJOIN:    • TEXTJOIN, CONCAT &, TEXTJOINIF  
SPLIT/ARRAYFORMULA:    • Google Sheets: Dynamic Arrays - SORTN...