Power Query & Power Pivot Training Overview

Power Query Foundation

  • Introduction to Power Query
  • Exploring the Power Query editor
  • Uses for Power Query including:
    • Importing data from multiple sources
    • Working with different file types
    • Transforming data layouts
    • Exporting Queries to Tables and Pivot Tables
  • How to transform untidy data with Power Query
  • Using MERGE QUERIES in place of VLOOKUP
  • The Power Query UNPIVOT feature
  • Using MERGE QUERIES to compare lists
  • Using Power Query to append lists
  • Working with multiple files sources in Power Query
  • Importing data from other sources (e.g. CSV, Text, Word docs.)
  • Converting data with International dates and number formats

Power Query Advanced

  • Advanced Transformation techniques
  • Managing complex queries
  • Working with Conditional Logic in Power Query
  • Understanding the M language
  • Creating Parameters and Functions with the M language
  • Optimising queries

Power Pivot Foundation

  • Exploring the Power Pivot Window
  • Introduction to the Data Model and Power Pivot
  • Adding Tables to the Data Model
  • Creating relationships between Data Tables
  • Building a Pivot Table from the Data Model

Power Pivot Advanced

  • Introduction to the DAX language
  • Creating DAX Expressions including:
    • ALL
    • SUM
    • CALCULATE
    • COUNT
    • DATE
    • DISTINCTCOUNT
    • MONTH
    • RELATED
    • RELATEDTABLE
  • Shaping the Data Model with Calculated Columns with RELATED() & RELATETABLE() DAX Functions
  • Creating Measures using the CALCULATE() DAX Function
  • Creating Measures using the COUNT() & DISTINTCOUT() Dax Functions
  • Building advanced Pivot Tables and Pivot Charts
  • Creating Dashboards in Power Pivot with Slicers