# Highline Excel 2016 Class 03

Data Analysis Fundamentals: PivotTables, Power Query & Data Model

### DESCRIPTION

In this video learn about the fundamentals of Data Analysis and Business Intelligence in Excel 2016: Sort, Filter, PivotTables, Power Query, Power Pivot Data Model:
(00:00:05) Introduction
(00:01:39) Sort
(00:03:02) Sorting one column
(00:03:23) Sorting multiple columns
(00:09:06) Sorting Mixed Data
(00:10:50) Filter feature
(00:13:22) Filter Drop-down Arrows to see Unique List
(00:15:34) Filter Different Data Types
(00:18:30) Filter to Extract Records
(00:20:54) OR Logical Test (OR Criteria) Discussion
(00:22:52) AND Logical Test (AND Criteria) Discussion
(00:28:23) BETWEEN and NOT Criteria
(00:30:57) PivotTable. Discussion of Crosstabulated tables and PivotTables as “Calculations with Criteria”, both AND Criteria and OR Criteria.
(00:33:05) PivotTable Basics: 1) Drag and Drop Field Names to add criteria to PivotTable, 2) Cross Tabulated Table, 3) Layout Formatting, 4) Number Formatting
(00:40:21) Creating a Custom Style for a PivotTable
(00:44:48) Name PivotTable
(00:46:12) Create PivotTable using “Summarize Values By”, which allows us to change the Aggregate Functions like: SUM, COUNT, AVERAGE.
(00:51:49) Group Dates by Month and Year
(00:54:50) Create PivotTable using “Show Values As” to calculate “% of Column Total”.
(00:56:09) Hide items in Slicer
(00:57:24) Connect Multiple Slicers to Multiple PivotTables.
(00:58:57) Sort in PivotTable.
(01:00:11) Create multiple PivotTables with a single click using “Show Report Filter Pages”
(01:03:10) Why we need Power Query and Power Pivot Data Model
(01:05:21) Introduction to Power Query (Get & Transform)
(01:07:04) Power Query Example 1: Clean and Transform Data Table, Create PivotTable Based on Power Query Update, 3) Add new data to table and Refresh to update Query and PivotTable
(01:18:25) Power Query to Unpivot a Crosstabulated Table into a Proper Data Set.
(01:24:54) Introduction to Power Pivot and the Data Model
(01:25:32) Power Query to import multiple Text File tables with over one million records combine them into a single Table. We will use the “From File, From Folder” option.
(01:33:20) Load the million records in Power Query into the Power Pivot Data Model.
(01:34:42) Add an Excel Table into the Power Pivot Data Model
(01:35:30) Update Power Query
(01:36:36) Build a relationship between tables in the Power Pivot Data Model.
(01:38:08) Build PivotTable from Millions of Records from Two Tables
(01:40:34) Add new Text File to Folder and Update PivotTable.
(01:41:22) Summary

Highline Excel 2016 Professional Class taught by Michael excelisfun Girvin