Advanced Excel: Power Query & Power Pivots

Course Info

Length: 1 Week

City: London

Type: In Classroom

Available Dates

  • Apr-15-2024

    London

  • May-20-2024

    London

  • June-17-2024

    London

  • July-15-2024

    London

  • Aug-19-2024

    London

  • Sep-16-2024

    London

  • Oct-21-2024

    London

  • Nov-18-2024

    London

  • Dec-16-2024

    London

Dates in Other Venues

  • Apr-01-2024

    Paris

  • Apr-01-2024

    Singapore

  • Apr-08-2024

    Barcelona

  • Apr-08-2024

    Dubai

  • Apr-15-2024

    Amsterdam

  • Apr-22-2024

    Paris

  • Apr-22-2024

    Kuala Lumpur

  • Apr-29-2024

    Singapore

  • Apr-29-2024

    Istanbul

  • May-06-2024

    Paris

  • May-06-2024

    Singapore

  • May-13-2024

    Barcelona

  • May-13-2024

    Dubai

  • May-20-2024

    Amsterdam

  • May-27-2024

    Kuala Lumpur

  • May-27-2024

    Istanbul

  • June-03-2024

    Singapore

  • June-03-2024

    Paris

  • June-10-2024

    Dubai

  • June-10-2024

    Barcelona

  • June-17-2024

    Amsterdam

  • June-24-2024

    Kuala Lumpur

  • June-24-2024

    Istanbul

  • July-01-2024

    Singapore

  • July-01-2024

    Paris

  • July-08-2024

    Dubai

  • July-08-2024

    Barcelona

  • July-15-2024

    Amsterdam

  • July-22-2024

    Paris

  • July-22-2024

    Kuala Lumpur

  • July-29-2024

    Istanbul

  • July-29-2024

    Singapore

  • Aug-05-2024

    Paris

  • Aug-05-2024

    Singapore

  • Aug-12-2024

    Dubai

  • Aug-12-2024

    Barcelona

  • Aug-19-2024

    Amsterdam

  • Aug-26-2024

    Istanbul

  • Aug-26-2024

    Kuala Lumpur

  • Sep-02-2024

    Paris

  • Sep-02-2024

    Singapore

  • Sep-09-2024

    Barcelona

  • Sep-09-2024

    Dubai

  • Sep-16-2024

    Amsterdam

  • Sep-23-2024

    Paris

  • Sep-23-2024

    Kuala Lumpur

  • Sep-30-2024

    Singapore

  • Sep-30-2024

    Istanbul

  • Oct-07-2024

    Paris

  • Oct-07-2024

    Singapore

  • Oct-14-2024

    Barcelona

  • Oct-14-2024

    Dubai

  • Oct-21-2024

    Amsterdam

  • Oct-28-2024

    Istanbul

  • Oct-28-2024

    Kuala Lumpur

  • Nov-04-2024

    Paris

  • Nov-04-2024

    Singapore

  • Nov-11-2024

    Barcelona

  • Nov-11-2024

    Dubai

  • Nov-18-2024

    Amsterdam

  • Nov-25-2024

    Istanbul

  • Nov-25-2024

    Kuala Lumpur

  • Dec-02-2024

    Singapore

  • Dec-02-2024

    Paris

  • Dec-09-2024

    Barcelona

  • Dec-09-2024

    Dubai

  • Dec-16-2024

    Amsterdam

  • Dec-23-2024

    Paris

  • Dec-23-2024

    Kuala Lumpur

  • Dec-30-2024

    Istanbul

  • Dec-30-2024

    Singapore

Course Details

Course Outline

5 days course

 

Key functions to prepare data for pivot table reporting
 
  • Table format
  • Lookup functions
  • Text functions
  • Naming cells

 

Advanced techniques in creating and customizing pivot tables
 
  • Number and cell format
  • Report layout
  • Calculation in value field
  • Grouping and un-grouping fields

 

Default and customized sorting and filtering
 
  • Sorting using custom list
  • Creating calculated field
  • Filtering using slicers and timelines
  • Connecting multiple pivot tables to one set of slicers
  • Customizing reports using the GetPivotData option

 

 
Power Query: A must-have skill
 
  • Introduction to this new feature
  • Where does Power Query fit in the Power family!
  • Get and transform: Link your Excel to external other data sources
    • Excel files
    • Text files
    • Web
    • SQL
  • Creating and editing the Query
  • Get data from: Tables, files and folders
  • Power Query to clean up data
  • Practical examples:
    • UnPivotting data
    • Working with nested column headers and merged cells
    • Naming, merging, splitting and removing columns
    • Filtering rows in different ways
    • Transforming and formatting data
    • Combining queries: Merge and Append
      • The different types of joining data

 

Analyzing disparate data sources with pivot tables
 
  • Utilizing pivot table wizard
  • Using internal data model
  • Building pivot tables using external data sources

 

The new world of PowerPivot
 
  • Benefits and drawbacks of PowerPivot
  • Merging data from multiple tables without using Vlookup
  • Creating better calculations using the DAX Formulas
  • Using DAX to create calculated fields
  • Calculate and Related functions

Course Video