Excel Business Intelligence: Power Pivot, DAX and Data Modeling

Develop fundamental, in-demand business intelligence skills using Microsoft Excel's Power Query, Power Pivot, and Data Analysis Expressions (DAX).

Getting Started

• Welcome
• Important: Versions and compatibility
• Download the course exercise files
• Set expectations

1. Intro to the Power Excel Series

• The power Excel workflow
• The best thing to happen to Excel in 20 years
• When to use Power Query and Power Pivot in Excel

2. Power Query

• 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

3. Data Modeling 101

• 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

4. Power Pivot and DAX 101

• 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

5. Common DAX Functions

• 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

Conclusion

• Next steps