This comprehensive class combines two powerful Excel tools—Power Query and Power Pivot with DAX—to give you end-to-end control over your data, from raw import to final analysis.

We begin with Power Query, Excel’s built-in data connection and transformation technology, which enables you to discover, connect, combine, and clean data from a variety of sources. You’ll learn to shape your data—removing unnecessary columns, changing data types, merging tables, and more—so it’s ready for analysis. We start from the ground up, assuming no prior experience, and progress to more advanced queries.

Once your data is prepared, we move into Power Pivot & DAX, Excel’s powerful modeling and calculation engine. You’ll learn how to create data models, build relationships between tables, and write DAX (Data Analysis Expressions) formulas to perform advanced calculations. This will allow you to summarize and analyze large datasets efficiently, unlocking deeper insights to support timely business decisions.

By the end of this course, you’ll be able to pull in data from multiple sources, clean and shape it in Power Query, model it in Power Pivot, and create robust, dynamic reports that transform raw information into actionable intelligence. This class is ideal for Excel users who want to take their data skills to the next level with tools used by analysts and business intelligence professionals worldwide.

For the best results, participants should be familiar with the topics covered in the Excel Introduction, Excel Intermediate Part 1, and Excel Intermediate Part 2 classes.

• What is Power Pivot, Power Query, and DAX
• The Power Suite Workflow
• Power Query Examples
• Power Pivot Examples

• Common Usage Patterns
• Data Connectors in Excel
• Load to a Table
• Load to a Pivot Table
• Load to a Pivot Chart
• Only Create Connection
• Add to the Data Model

• Basic Data Transformations
• Text Data Transformations
• Numerical Data Transformations
• Date Data Transformations
• Indexes and Conditional Columns
• Grouping and Summarizing Data
• Transposing Data
• Pivoting and UnPivoting Data

• Updating Workbook Queries
• Merging Multiple Queries
• Appending Queries
• Utilizing a Folder of Data Files
• Power Query Editor
• Power Query Best Practices

• Understanding Excel’s Data Model
• Data View versus Diagram View
• Database Normalization
• Data Model Schema
• Data Model Best Practices
• Data Tables versus Lookup Tables
• Relationships versus Merged Tables
• Creating Table Relationships
• Updating Table Relationships
• Active versus Inactive Relationships
• Relationship Cardinality
• Connecting Multiple Data Tables
• Data Filter Direction
• Hiding Fields from Client Tools
• Defining Hierarchies

• Creating a Power Pivot Table
• Power Pivots versus Normal Pivots
• Data Analysis Expression (DAX) Overview
• Calculated Columns
• DAX Measures
• Creating Implicit Measures
• Creating Explicit Measures (AutoSum)
• Creating Explicit Measures (Power Pivot)
• Filter Context
• Dimensions vs Measures
• Best Practices