Excel Power Query Power Pivot & Dax

Book Now

Course Description

It’s time to master Excel’s powerful trio of self-service BI tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX). If you’re serious about business intelligence, these are essential skills to have

Curriculum

• Intro to Power Excel
  Power Excel Workflow
  The Best add-ons for Excel in 20 Years
  When to use PQ & Power Pivot

• Power Query
  Meet Power Query (aka Get & Trans)
  The Query Editor
  Data Loading Options
  Basic Table Transformations
  Text-Specific Tools
  Date-Specific Tools
  Adding Index & Conditional Columns
  Grouping & Aggregating Data
  Pivoting & Unpivoting Data
  Merging Queries (aka Vlookup+)
  Appending Queries
  Connecting to a Folder of Files
  Additional Data Connectors

• Data Modeling 101
  Meet Excel's Data Model
  Data vs Diagram View
  Data Tables vs Lookup Tables
  Relationship vs Merged Tables
  Creating Table Relationships
  Modifying Table Relationships
  Active vs Inactive Relationships
  Relationship Cardinality
  Connecting Multiple Data Tables
  Filter Direction

• Power Pivot & DAX 101

  Creating a Power PivotTable
  Power Pivots vs Normal Pivots
  Introduction to DAX
  Calculated Columns
  Intro to DAX Measures
  Step by Step Measure Calculation
  Calculated Columns vs Measures

• Common DAX Functions
  DAX Formula Syntax & Operators
  Common DAX Functions
  Basic Math & Stat (Count, DistintCount)
  Logical Functions (IF/AND/OR)
  Common Date & Time Functions
  The Calculate Functions
  Adding Filter Context
  Removing Filter Context with ALL
  Joining Data with Related Function
  Time Intelligence Formulas
  MTD, YTD, Same Period Last Year Etc.