Develop fundamental, in-demand business intelligence skills using Microsoft Excel's Power Query, Power Pivot, and Data Analysis Expressions (DAX).
Excel Business Intelligence: Power Pivot, DAX and Data Modeling
Overview
Syllabus
Getting Started
- Welcome
- Important: Versions and compatibility
- Download the course exercise files
- Set expectations
- The power Excel workflow
- The best thing to happen to Excel in 20 years
- When to use Power Query and Power Pivot in Excel
- Power Query introduction
- Meet Power Query (known as Get & Transform)
- The Query Editor
- Options for loading data in Excel
- Basic Power Query table transformations
- Text-specific query editing tools
- Number-specific query editing tools
- Date-specific query editing tools
- Create a rolling calendar with Power Query
- Add index and conditional columns with Power Query
- Group and aggregate data with Power Query
- Modify Excel workbook queries
- Merge queries
- Append queries
- Connect Excel to a folder of files
- Excel Power Query best practices
- Pivot and unpivot data with Power Query
- Data modeling introduction
- Meet the Excel data model
- Data versus diagram view
- Database normalization
- Data tables versus lookup tables
- Relationships versus merged tables
- Create table relationships
- Modify table relationships
- Active versus inactive relationships
- Relationship cardinality
- Connect multiple data tables
- Filter direction
- Hide fields from client tools
- Define hierarchies
- Data model best practices
- Introduction to Power Pivot and DAX
- Create a Power Pivot table
- Power Pivots versus normal pivots
- Introduction to Data Analysis Expressions (DAX)
- Calculated columns
- DAX measures
- Create implicit measures
- Create explicit measures (AutoSum)
- Create explicit measures (Power Pivot)
- Understand filter context
- Step-by-step measure calculation
- Recap: Calculated columns versus measures
- Power Pivot best practices
- Introduction to DAX functions
- DAX formula syntax and operators
- Common DAX function categories
- Basic math and stats functions
- COUNT, COUNTA, DISTINCTCOUNT, and COUNTROWS
- Logical functions (IF, AND, and OR)
- Switch and Switch (TRUE)
- Text functions
- The CALCULATE function
- Add filter context with FILTER: Part 1
- Add filter context with FILTER: Part 2
- Remove filter context with ALL
- Join data with RELATED
- Iterator ("X") functions: SUMX
- Iterator ("X") functions: RANKX
- Basic date and time functions
- Time intelligence formulas
- Speed and performance considerations
- DAX best practices
- Final section
- Data visualization options
- Wrapping up
- Next steps
Taught by
Maven Analytics and Chris Dutton
