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: https://youtu.be/4pdwoLGhzJ4
TEXTJOIN: https://youtu.be/sF9BaxsCoU8
SPLIT/ARRAYFORMULA: https://youtu.be/14Uo4gMRsFw?t=112