Скачать с ютуб Unpivot in Google Sheets with FLATTEN: Column pairs, auto expand++ в хорошем качестве

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...