EXCEL POWER QUERY & POWER PIVOT

Level: Advanced | Delivery Channel: In-Person Classroom or Virtually Online | CPE Credits: 8 Credits | Duration: 8 Hours

This course in an introduction to Power Query, Power Pivot, and DAX (Data Analysis Expressions) language. It provides a solid foundation on importing and transforming data, establishing relationships between tables, and performing DAX calculations that exceed the limits of standard Pivot Tables. Then creating and analyzing multi-table Pivot Tables.

WHO IS THIS CLASS FOR

N

Individuals with a strong Excel foundation skill set that want to level up, especially in the area of Power Query and Power Pivot to extract, transform, and load multi-table datasets into powerful Pivot Tables.

N

Team members that on regular basis need to pull data from disparte datasets to create unified data models that can be used to create multi-table Pivot Tables.

WHAT YOU'LL LEARN

Power Suite Overview
  • What is Power Pivot, Power Query, and DAX
  • The Power Suite Workflow
  • Power Query Examples
  • Power Pivot Examples
Power Query Data Loading Options
  • 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
Power Query Standard Transformations
  • 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
Power Query Merging, Appending, and Updating Queries
  • Updating Workbook Queries
  • Merging Multiple Queries
  • Appending Queries
  • Utilizing a Folder of Data Files
  • Power Query Editor
  • Power Query Best Practices
Data Modeling
  • 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
Power Pivot and DAX
  • 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
Popular DAX Functions
  • Introduction
  • DAX Formula Syntax and Operators
  • Common DAX Function Categories
  • Basic Math and Statistical Functions
  • COUNT, COUNTA, DISTINCTCOUNT, and COUNTROWS Functions
  • Logical Functions: IF, AND, OR
  • SWITCH Function
  • Text Functions: LEN, CONCATENATE, UPPER, LOWER, PROPER, LEFT, MID, RIGHT, SEARCH, SUBSTITUTE
  • CALCULATE Function
  • Adding Filter Context with FILTER
  • Removing Filter Context with ALL
  • Joining Data with RELATED Function
  • Iterator (“X”) Functions: SUMX
  • Iterator (“X”) Functions: RANKX
  • Date and Time Functions
  • Time Intelligence Formulas
  • Speed and Performance Factors
  • DAX Best Practices

WHAT YOU WILL GET

  • Class Binder with Step by Step Exercises and Challenge Exercises
  • Microsoft Excel Exercise Files with 1 year access to the files
  • Double-Sided Laminated Keyboard Shortcut Guide

WHAT TO BRING TO CLASS

  • We provide everything you need for our in-person classes. You just need to show up.
  • Come dressed for comfort. Jeans, Shorts, Yoga pants are all welcome!
  • Come ready to learn! Our classes are an intensive learning experience.

CLASS SCHEDULE

Date Location Price Registration
June 4, 2026
9:15 AM to 5:00 PM		 Dallas Fort Worth (Hurst, TX) $595.00 Register
August 13, 2026
9:15 AM to 5:00 PM		 Dallas Fort Worth (Hurst, TX) $595.00 Register
October 8, 2026
9:15 AM to 5:00 PM		 Dallas Fort Worth (Hurst, TX) $595.00 Register
December 17, 2026
9:15 AM to 5:00 PM		 Dallas Fort Worth (Hurst, TX) $595.00 Register

What exactly is the "Power Suite" covered in this class?

This course covers three critical components of modern Excel: Power Query (for connecting to and cleaning data), Power Pivot (for building sophisticated data models), and DAX (the formula language used to create advanced calculations). Together, they allow you to handle much larger datasets than standard Excel and automate your entire reporting workflow.

Are there any prerequisites for this course?

Yes. To get the most out of this 1-day intensive, you should be familiar with the concepts taught in our Excel Introduction, Excel Formula and Functions, and Excel Pivot Tables.

I already use PivotTables; how is Power Pivot different?

Standard PivotTables are usually limited to a single flat table of data. Power Pivot allows you to connect multiple tables (like Sales, Products, and Geography) without using thousands of XLOOKUP formulas. It also supports much larger datasets—millions of rows—which would exceed the standard Excel row limit.

Will I learn how to automate data cleaning?

Absolutely. The first half of the class focuses on Power Query (ETL). You will learn how to connect to various data sources (CSV, SQL, Web) and "shape" that data—removing duplicates, unpivoting columns, and transforming text—so that your data is perfectly cleaned every time you hit "Refresh".

Can I use these skills in Power BI?

Yes. One of the biggest benefits of this class is that the Power Query and Power Pivot (DAX) engines in Excel are nearly identical to those in Microsoft Power BI. The skills you learn here will give you a significant head start if you choose to move into full-scale Business Intelligence tools later.

How much does the class cost?

This standard price is $595, but if you sign up 30 days in advance you get an early bird price of $565.

Does this class qualify for CPE credits?

Yes! This class is accredited through the Texas State Board of Public Accountancy and earns 8 CPE (Continuing Professional Education) credits. You will receive a certificate of completion at the end of class.

Is there a live online option?

Yes. We offer this class in both in-person Dallas Fort Worth, Houston, Austin, Oklahoma City and live online formats. The online class is always led by a live instructor — not a pre-recorded video.

What do I need to bring to the in-person class?

Nothing! We provide a full-size training computer, class materials, and snacks and drinks throughout the day. Just bring yourself.

What materials do I take home after class?

You'll leave with the Excel practice file used during class and a student handbook so you can continue practicing on your own schedule.

How big are the public classes?

In-person public classes are small — up to 6 students — so you get plenty of individual attention from the instructor.

Can my whole team take this class together?

Absolutely. We offer private group training tailored to your team's schedule, location, and skill level.

Is the training hands-on or lecture-based?

Yes. Our classes are highly interactive with guided labs and exercises so participants practice every concept rather than just watching demonstrations.

I have more questions?

Our AI Chatbot has a wealth of knowledge and can answer most standards questions. Feel free to use our AI Chatbot in bottom right of the screen, feel free to use our Contact Form, and feel free to call us at 817-841-9560.

Got More Questions?

We are here to help! Call us at 817-841-9560 or use our Contact Form to get in touch with us.