The Data Analysis with Excel + Power BI Bundle

Your First Program

Getting Started

• Course Structure & Outline - 1:43
• DOWNLOAD: Course Resources
• Introducing the Adventure Works Project - 3:02
• Setting Expectations - 2:21

Introducing Power BI Desktop

• Introduction - 0:26
• Meet Power BI - 5:30
• Downloading Power BI & Adjusting Settings - 4:05
• IMPORTANT: Regional Settings
• The Power BI Interface & Workflow - 2:34
• Helpful Resources - 1:44
• UPDATE: New Power BI Ribbon (2020) - 7:15
• QUIZ: Introducing Power BI Desktop

Connecting & Shaping Data

• Introduction - 0:30
• Types of Data Connectors - 3:53
• The Query Editor - 4:28
• Basic Table Transformations - 10:03
• Text-Specific Tools - 14:54
• Number-Specific Tools - 8:37
• Date-Specific Tools - 8:53
• PRO TIP: Creating a Rolling Calendar - 7:08
• Index & Conditional Columns - 8:30
• Grouping & Aggregating Data - 7:33
• Pivoting & Unpivoting - 8:04
• Merging Queries - 6:41
• Appending Queries - 6:29
• PRO TIP: Appending Files from a Folder - 7:38
• Data Source Settings - 5:05
• Query Refresh Settings - 3:06
• Additional Data Types & Categories - 7:19
• Defining Hierarchies - 4:35
• PRO TIP: Importing Models from Excel - 5:40
• Data Connection Best Practices - 2:09
• QUIZ: Connecting & Shaping Data
• HOMEWORK: Connecting & Shaping Data
• HOMEWORK SOLUTION: Connecting & Shaping Data - 12:36

Creating a Data Model

• Introduction - 0:19
• What is a "Data Model"? - 5:17
• Database Normalization - 4:07
• Data Tables vs. Lookup Tables - 7:25
• Relationships vs. Merged Tables - 2:31
• Creating Table Relationships - 4:34
• "Snowflake" Schemas - 3:09
• Managing & Editing Relationships - 2:44
• Active vs. Inactive Relationships - 2:45
• Relationship Cardinality - 5:08
• Connecting Multiple Data Tables - 8:26
• Understanding Filter Flow - 6:33
• Two-Way Filters (Use with Caution!) - 13:16
• Hiding Fields from Report View - 4:15
• UPDATE: New Model View - 8:32
• Data Model Best Practices - 1:34
• QUIZ: Creating a Data Model
• HOMEWORK: Creating a Data Model
• HOMEWORK SOLUTION: Creating a Data Model - 14:09

Adding Calculated Fields with DAX

• Introduction - 0:32
• Meet DAX - 1:03
• Intro to Calculated Columns - 7:33
• Intro to Measures - 2:51
• RECAP: Calculated Columns vs. Measures - 1:34
• Adding Columns & Measures - 3:12
• Implicit vs. Explicit Measures - 6:27
• Filter Context Examples - 8:07
• Step-by-Step Measure Calculation - 2:39
• DAX Syntax & Operators - 3:49
• Common DAX Function Categories - 3:16
• Basic Date & Time Functions - 12:24
• Logical Functions - 10:06
• Text Functions - 9:24
• Joining Data with RELATED - 8:54
• Basic Math & Stats Functions - 14:01
• COUNT Functions - 7:46
• IMPORTANT: Order Line Items - 6:21
• The CALCULATE Function - 13:06
• CALCULATE & ALL - 9:25
• CALCULATE & FILTER - 9:37
• Iterator ("X") Functions - 12:27
• DAX Time Intelligence Formulas - 12:29
• DAX Best Practices - 2:57
• QUIZ: Adding Calculated Fields
• HOMEWORK: Adding Calculated Fields
• HOMEWORK SOLUTION: Adding Calculated Fields - 15:05

Visualizing Data with Reports

• Introduction - 0:29
• The "Report" View - 5:33
• Adding Simple Objects - 3:07
• Inserting Basic Charts & Visuals - 5:47
• UPDATE: Conditional Formatting - 2:58
• Formatting Options - 9:51
• Report Filtering Options - 11:09
• DEMO: Matrix Visuals - 6:38
• DEMO: Date Slicers - 7:14
• DEMO: Cards & KPIs - 9:24
• DEMO: Text Cards - 6:14
• DEMO: Map Visuals - 7:15
• DEMO: Treemaps - 3:21
• DEMO: Line & Area Charts - 6:23
• DEMO: Trend Lines & Forecasts - 5:36
• DEMO: Gauge Charts - 6:43
• Editing Report Interactions - 6:38
• Adding Drillthrough Filters - 5:08
• Adding Report Bookmarks - 5:49
• "What-If" Parameters - 11:47
• Managing & Viewing Roles - 5:27
• PREVIEW: Custom Visuals (Log-In Required) - 2:05
• PREVIEW: Designing for Phone vs. Desktop - 2:54
• PREVIEW: Publishing to Power BI Service - 2:27
• Data Visualization Best Practices - 1:35
• QUIZ: Visualizing Data with Reports
• HOMEWORK: Visualizing Data with Reports
• HOMEWORK SOLUTION: Visualizing Data with Reports - 7:22

NEW: Artificial Intelligence (AI) Visuals

• The Q&A Visual - 10:54
• The Key Influencers Visual (Part 1) - 12:34
• The Key Influencers Visual (Part 2) - 5:11
• IMPORTANT: Correlation vs. Causation - 4:24
• The Decomposition Tree Visual - 8:59

Wrapping Up

• More from Maven Analytics
• REFERENCE: Monthly Power BI Updates

• Course Structure & Outline - 1:32
• Getting to Know the IMDb Movie Database - 2:21
• Setting Expectations - 1:14

• Why PivotTables? - 4:10
• Structuring the Source Data - 2:00
• Inserting Your First PivotTable - 3:13
• Navigating the Field List - 7:43
• Analyze & Design Options - 1:33
• Selecting, Clearing, Moving & Copying Pivots - 5:14
• Refreshing & Updating Pivots - 7:30
• PRO TIP: Dealing with Growing Source Data - 6:49
• Removing & Reviving Data from Cache - 5:41
• How PivotTables ACTUALLY Work - 3:39
• QUIZ: PivotTable 101
• HOMEWORK: PivotTable 101

• Number Formatting - 2:12
• PRO TIP: Formatting Empty Cells - 1:41
• Table Layouts & Styles - 5:30
• PRO TIP: Using Tabular Layouts to Create New Tables - 2:20
• Customizing Headers & Labels - 1:16
• Adding Conditional Formats - 6:51
• PRO TIP: Data Bars with Invisible Text - 2:13
• Advanced Conditional Formatting - 6:51
• QUIZ: PivotTable Formatting
• HOMEWORK: PivotTable Formatting

• Basic Sorting Options - 5:01
• PRO TIP: Incorrect Alphabetical Sorting - 2:01
• Label Filters & Manual Selections - 8:15
• PRO TIP: Label Filters with Wildcards - 3:15
• Value Filters - 3:10
• PRO TIP: Enabling Multiple Filters - 2:42
• Grouping Data - 3:23
• Automatic Date Grouping - 4:25
• Filtering with Slicers & Timelines - 4:51
• Breaking Out Report Filter Pages - 4:13
• QUIZ: Sorting, Filtering & Grouping
• HOMEWORK: Sorting, Filtering & Grouping

• Summarizing Values - 4:45
• PRO TIP: Avoiding the "Count Of" Trap - 3:49
• "Show Values As" Calculations - 3:35
• Show Values As: % of Column/Row - 3:47
• Show Values As: % of Parent - 3:02
• Show Values As: Difference From - 4:41
• Show Values As: Running Total - 2:44
• Show Values As: Rank - 2:36
• Show Values As: Index - 8:06
• Inserting Calculated Fields - 6:30
• Calculations in Pivots vs. Raw Data - 4:32
• Calculating Using Counts (Part 1) - 7:20
• Calculating Using Counts (Part 2) - 4:19
• Calculated Items (NOT RECOMMENDED) - 6:11
• Solve Order & List Formula Tools - 4:54
• QUIZ: Calculated Values & Fields
• HOMEWORK: Calculated Values & Fields

• Intro to PivotCharts - 2:26
• DEMO: Column Chart - 4:13
• DEMO: Pie & Donut Charts - 5:46
• DEMO: Clustered Bar Chart - 3:25
• PRO TIP: Prevent Charts from Resizing with Cells - 4:45
• Changing Chart Types on the Fly - 3:18
• DEMO: Stacked Area Chart - 5:46
• PivotChart Layouts & Styles - 4:03
• Moving PivotCharts to New Sheets - 2:09
• Applying Slicers & Timelines to Multiple Charts - 5:01
• DEMO: Building a Dynamic Dashboard - 13:22
• QUIZ: PivotCharts
• HOMEWORK: PivotCharts

• Setting Expectations - 1:45
• U.S. Voters (2012) - 10:33
• HOMEWORK: U.S. Voters (2012)
• San Francisco Salaries - 13:06
• HOMEWORK: San Francisco Salaries
• Shark Attack Records - 10:31
• HOMEWORK: Shark Attack Records
• Stock Market Data - 12:25
• HOMEWORK: Stock Market Data
• Baseball Team Statistics - 14:16
• HOMEWORK: Baseball Team Statistics
• San Diego Burrito Ratings - 16:40
• HOMEWORK: San Diego Burrito Ratings
• Daily Weather Conditions - 12:41
• HOMEWORK: Daily Weather Conditions
• Spartan Race Facebook Posts - 14:37
• HOMEWORK: Spartan Race Facebook Posts

• More from Maven Analytics

• Course Structure & Outline - 1:22
• IMPORTANT: Versions & Compatibility - 3:15
• DOWNLOAD: Course Resources - 1:16
• Setting Expectations - 1:53

• The "Power Excel" Workflow - 3:50
• "Best Thing to Happen to Excel in 20 Years" - 8:18
• When to use Power Query & Power Pivot - 0:44
• QUIZ: Intro to "Power Excel"
• HOMEWORK: Intro to "Power Excel"

• Introduction - 0:27
• Meet Power Query (aka "Get & Transform") - 2:40
• The Query Editor - 3:17
• Data Loading Options - 1:40
• IMPORTANT: Regional Formatting
• Basic Table Transformations - 8:06
• Text-Specific Tools - 10:17
• Number-Specific Tools - 7:41
• Date-Specific Tools - 7:39
• PRO TIP: Creating a Rolling Calendar - 5:02
• Adding Index & Conditional Columns - 7:44
• Grouping & Aggregating Data - 7:10
• Pivoting & Unpivoting - 8:52
• Modifying Workbook Queries - 4:18
• Merging Queries - 7:43
• Appending Queries - 5:49
• Connecting to a Folder of Files - 9:32
• Power Query Best Practices - 2:44
• QUIZ: Power Query
• HOMEWORK: Power Query

• Introduction - 0:28
• Meet Excel's "Data Model" - 3:38
• Data vs. Diagram View - 2:33
• Database Normalization - 4:14
• Data Tables vs. Lookup Tables - 5:40
• Relationships vs. Merged Tables - 2:07
• Creating Table Relationships - 6:54
• Modifying Table Relationships - 2:06
• Active vs. Inactive Relationships - 3:55
• Relationship Cardinality - 5:46
• Connecting Multiple Data Tables - 5:59
• Filter Direction - 5:08
• Hiding Fields from Client Tools - 4:59
• Defining Hierarchies - 3:53
• Data Model Best Practices - 1:38
• QUIZ: Data Modeling 101
• HOMEWORK: Data Modeling 101

• Introduction - 0:29
• Creating a "Power" PivotTable - 5:27
• "Power" Pivots vs. "Normal" Pivots - 2:48
• Intro to Data Analysis Expressions (DAX) - 1:12
• Calculated Columns - 8:39
• DAX Measures = Supercharged Calculated Fields - 2:24
• Creating Implicit Measures - 1:59
• Creating Explicit Measures (AutoSum) - 7:08
• Creating Explicit Measures (Power Pivot) - 7:42
• Understanding Filter Context - 9:18
• Step-by-Step Measure Calculation - 12:06
• RECAP: Calculated Columns vs. Measures - 2:29
• Power Pivot Best Practices - 1:50
• QUIZ: Power Pivot & DAX 101
• HOMEWORK: Power Pivot & DAX 101

• Introduction - 0:27
• DAX Formula Syntax & Operators - 4:05
• Common DAX Function Categories - 3:54
• Basic Math & Stats Functions - 12:45
• COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS - 10:35
• HOMEWORK: Math & Stats Functions
• Logical Functions (IF/AND/OR) - 16:49
• SWITCH & SWITCH(TRUE) - 10:33
• Text Functions - 10:53
• HOMEWORK: Logical & Text Functions
• The CALCULATE Function - 14:39
• Adding Filter Context with FILTER (Part 1) - 9:20
• Adding Filter Context with FILTER (Part 2) - 14:23
• Removing Filter Context with ALL - 9:06
• HOMEWORK: CALCULATE, FILTER & ALL
• Joining Data with RELATED - 3:39
• Iterator ("X") Functions: SUMX - 12:20
• Iterator ("X") Functions: RANKX - 5:33
• HOMEWORK: Iterator Functions
• Basic Date & Time Functions - 4:58
• Time Intelligence Formulas - 17:02
• HOMEWORK: Time Intelligence
• Speed & Performance Considerations - 3:02
• DAX Best Practices - 2:03
• QUIZ: Common DAX Functions

• Introduction - 0:53
• Data Visualization Options - 4:35
• SNEAK PEEK: PowerBI - 5:55
• More from Maven Analytics