Completed
1. 00:00 Introduction
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Excel Formula to UnPivot: Convert Cross-Tab Table to Proper Table Using LAMBDA - EMT 1887
Automatically move to the next video in the Classroom when playback concludes
- 1 1. 00:00 Introduction
- 2 2. 00:03 What we did in EMT 1886: Power Query UnPivot
- 3 3. 00:17 ExcelLambda left comment about UnPivot Formula
- 4 4. 00:33 Why we might want a formula to UnPivot
- 5 5. 01:05 Three Part Solution: Build Formulas, LET function to remove repetition, LAMBDA function to create re-usable function
- 6 6. 01:22 Build formula to unpivot a cross tabulated table, piece by piece
- 7 7. 01:22 IFS Function formula element to deliver an #N/A error when data is missing in cross tab table.
- 8 8. 02:15 SEQUENCE & ROWS functions used with IFS to deliver row positions from cross tabulated table row headers row criteria
- 9 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 10. 04:12 IFS, SEQUENCE, COLUMNS and TOCOL functions to deliver column positions from cross tabulated table column headers column criteria
- 11 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 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 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 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 element…
- 15 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 16. 08:56 Why we use the LET function
- 17 17. 09:42 Testing variables in the LET function
- 18 18. 12:18 LAMBDA function to define a re-usable worksheet function. We use LAMBDA to define a new UnPivot function
- 19 19. 12:30 Create arguments in the new LAMBDA defined function
- 20 20. 14:07 Test the new LAMBDA function
- 21 21. 15:40 Paste LAMBDA function into the Defined Name Dialog Box
- 22 22. 16:22 Use the new LAMBDA function: UnPivot
- 23 23. 16:43 Bonus: Add field name argument to the MABDA function
- 24 24. 17:27 Summary
- 25 25. 17:35 Closing
