Excel Formula to UnPivot: Convert Cross-Tab Table to Proper Table Using LAMBDA - EMT 1887

1. 00:00 Introduction
2. 00:03 What we did in EMT 1886: Power Query UnPivot
3. 00:17 ExcelLambda left comment about UnPivot Formula
4. 00:33 Why we might want a formula to UnPivot
5. 01:05 Three Part Solution: Build Formulas, LET function to remove repetition, LAMBDA function to create re-usable function
6. 01:22 Build formula to unpivot a cross tabulated table, piece by piece
7. 01:22 IFS Function formula element to deliver an #N/A error when data is missing in cross tab table.
8. 02:15 SEQUENCE & ROWS functions used with IFS to deliver row positions from cross tabulated table row headers row criteria
9. 02:59 TOCOL function to skip #N/A errors when data is missing in cross tab table and you want to skip a record in the resulting table
10. 04:12 IFS, SEQUENCE, COLUMNS and TOCOL functions to deliver column positions from cross tabulated table column headers column criteria
11. 05:05 CHOOSEROWS function to generate a column, a proper field, in the resulting unpivoted table for the row headers row criteria in the cross tabulated table.
12. 05:52 CHOOSEROWS & TRANSPOSE functions to generate a column, a proper field, in the resulting unpivoted table for the column headers column criteria in the cross tabulated table.
13. 06:31 TOCOL, IF & IFS Functions to generate a column, a proper field, in the resulting unpivoted table for the values on the inside of the cross tabulated table.
14. 07:09 Mash three main formula elements into a single cell formula that uses the HSTACK function. Learn how to use the Clipboard and Ctrl + C, C keyboard shortcut to mash together formulas elements into a single formula.
15. 08:10 LET Function to remove repeating formulas elements and make the formula more efficient. Learn that the LET Function can define variables in a worksheet formula.
16. 08:56 Why we use the LET function
17. 09:42 Testing variables in the LET function
18. 12:18 LAMBDA function to define a re-usable worksheet function. We use LAMBDA to define a new UnPivot function
19. 12:30 Create arguments in the new LAMBDA defined function
20. 14:07 Test the new LAMBDA function
21. 15:40 Paste LAMBDA function into the Defined Name Dialog Box
22. 16:22 Use the new LAMBDA function: UnPivot
23. 16:43 Bonus: Add field name argument to the MABDA function
24. 17:27 Summary
25. 17:35 Closing