Excel Business Intelligence: Power Pivot, DAX and Data Modeling

Course details

It's time to get up to speed with Microsoft Excel's powerful trio of business intelligence tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX). If you're serious about a successful career in business intelligence, these skills are essential to master. This project-based course is designed to guide you through the entire business intelligence workflow from start to finish, including data prep, modeling, analysis, and visualization. Join instructor Chris Dutton as he demonstrates the basics of loading and transforming raw CSV files with Power Query, building a relational data model from scratch, and exploring and analyzing your model using Power Pivot and DAX measures. Along the way, you’ll practice applying your skills with hands-on demos and datasets from a fictional supermarket chain.

Concepts

Getting Started

01 - Welcome
02 - Important - Versions and compatibility
03 - Download the course exercise files
04 - Set expectations

1. Intro to the Power Excel Series

05 - The power Excel workflow
06 - The best thing to happen to Excel in 20 years
07 - When to use Power Query and Power Pivot in Excel

2. Power Query

08 - Power Query introduction
09 - Meet Power Query (known as Get & Transform)
10 - The Query Editor
11 - Options for loading data in Excel
12 - Basic Power Query table transformations
13 - Text-specific query editing tools
14 - Number-specific query editing tools
15 - Date-specific query editing tools
16 - Create a rolling calendar with Power Query
17 - Add index and conditional columns with Power Query
18 - Group and aggregate data with Power Query
19 - Modify Excel workbook queries
20 - Merge queries
21 - Append queries
22 - Connect Excel to a folder of files
23 - Excel Power Query best practices
24 - Pivot and unpivot data with Power Query

3. Data Modeling 101

25 - Data modeling introduction
26 - Meet the Excel data model
27 - Data versus diagram view
28 - Database normalization
29 - Data tables versus lookup tables
30 - Relationships versus merged tables
31 - Create table relationships
32 - Modify table relationships
33 - Active versus inactive relationships
34 - Relationship cardinality
35 - Connect multiple data tables
36 - Filter direction
37 - Hide fields from client tools
38 - Define hierarchies
39 - Data model best practices

4. Power Pivot and DAX 101

40 - Introduction to Power Pivot and DAX
41 - Create a Power Pivot table
42 - Power Pivots versus normal pivots
43 - Introduction to Data Analysis Expressions (DAX)
44 - Calculated columns
45 - DAX measures
46 - Create implicit measures
47 - Create explicit measures (AutoSum)
48 - Create explicit measures (Power Pivot)
49 - Understand filter context
50 - Step-by-step measure calculation
51 - Recap - Calculated columns versus measures
52 - Power Pivot best practices

5. Common DAX Functions

53 - Introduction to DAX functions
54 - DAX formula syntax and operators
55 - Common DAX function categories
56 - Basic math and stats functions
57 - COUNT, COUNTA, DISTINCTCOUNT, and COUNTROWS
58 - Logical functions (IF, AND, and OR)
59 - Switch and Switch (TRUE)
60 - Text functions
61 - The CALCULATE function
62 - Add filter context with FILTER - Part 1
63 - Add filter context with FILTER - Part 2
64 - Remove filter context with ALL
65 - Join data with RELATED
66 - Iterator ( X ) functions - SUMX
67 - Iterator ( X ) functions - RANKX
68 - Basic date and time functions
69 - Time intelligence formulas
70 - Speed and performance considerations
71 - DAX best practices
72 - Final section
73 - Data visualization options
74 - Wrapping up

Conclusion

75 - Next steps