ESA #4: PivotTable Grouping to Build Frequency Distributions & Power Query to Import CSV Data
Download Excel File: https://excelisfun.net/files/Ch01-ESA.xlsm
Download CSV File: https://excelisfun.net/files/Ch01-ESA-AutoData.csv
Learn about how to use Power Query to import and transform CSV File data and use the PivotTable to create Frequency Distributions for Categorical and Quantitative Data.
Topics:
1. (00:00) Introduction to topics in video
2. (00:53) Tabular Reporting goals for this video: 4 Different Frequency Distributions
3. (01:31) PivotTable to create Frequency Distribution from Nominal Data that is located in the worksheet
4. (02:52) What is the PivotTable Cache?
5. (03:19) Introduction to PivotTable User Interface
6. (05:07) Change calculation in PivotTable with: Summarize Values By and Shows Values As
7. (06:07) Alt Keyboard Efficiency Tricks to create PivotTable and other features
8. (09:48) Power Query to Import & Transform CSV file with table of data
9. (11:30) What is a CSV File? What is a Delimiter?
10. (12:38) Where is the Power Query tool in Excel?
11. (14:30) What are Data Types in Power Query? And why are they Important!
12. (15:40) Merge two fields to create one element field
13. (16:44) Load Power Query Table to Excel Worksheet
14. (17:57) Edit Query after it has been loaded
15. (19:30) Terms to describe the table, fields and data.
16. (20:07) Frequency Distributions for different types of data
17. (20:34) Frequency Distributions defined
18. (20:47) Frequency Distribution for Nominal Data, using a PivotTable.
19. (21:23) Frequency Distribution for Discrete Quantitative Data, using a PivotTable and the Grouping Feature
20. (22:47) How to Group in a PivotTable and create Upper and Lower Limits for Counting Categories.
21. (23:49) Use Field Settings to show categories without data.
22. (25:05) Change Grouping Categories
23. (25:36) Grouping Categories Created for Discrete Quantitative Data
24. (26:08) Frequency Distribution for Continuous Quantitative Data, using a PivotTable and the Grouping Feature
25. (27:11) Grouping Categories Created for Continuous Quantitative Data
26. (28:14) What happens to PivotTable when Source Data Changes?
27. (28:32) Refreshing a PivotTable to allow new data into Report
28. (29:04) Fixing Ambiguous Grouping Categories in a PivotTable with Decimal Number Data. Three Methods.
29. (31:30) How Grouping works with PivotTable Cache.
30. (32:33) How to create Multiple Grouping Categories from a Single Data Source using PivotTable Wizard QAT Button or Keyboard: Alt, D, P.
31. (33:53) Summary of video
32. (34:40) How to Printout Notes from Excel Workbook File
33. (35:13) Homework files to download from in Canvas
34. (36:12) Closing and Video Links