Great Shelford, Cambridge
1 day course
£595.00 (ex. VAT)

This comprehensive 2 day Microsoft Excel course is designed for those looking to extend their Excel knowledge and add Power Pivot and Power Queries to their skillset. This course will introduce what both of these features are and then, using working examples, will get you familiar with the concepts in practice. 

To truly get the most out of analysing your data within Excel, you need a practical knowledge of using power queries, lookup functions, DAX and power pivots. Our trainers will guide you through the process and show you some of the tricks of the trade to get where you need to faster.

By the end of this course, you will be able to use the Power Query and Power Pivot tools to retrieve, shape, analyse and report on the data within your spreadsheet.

Course Outline

What is Power Query & Power Pivot?

  • What is Power Query?
  • What is Power Pivot?
  • How the tools should be used together

Importing Data into Excel & the Model

  • From Text Files
  • From CSV files
  • From Web
  • From Folder
  • From Excel ranges and sheets

Understanding Power Query

  • Understanding the Power Query interface
  • Renaming and deleting steps
  • Cleaning messy data example
  • Refreshing a query
  • Power Query Options

Common Power Query Features

  • Unpivot Columns
  • Setting Data Types
  • Append Queries
  • Merge Queries
  • Using the Advanced Editor

Understanding Power Pivot

  • How to access Power Pivot
  • Understanding the Power Pivot Interface
  • Different Power Pivot Views

Creating the Data Model

  • Create table relationships
  • Edit and delete relationships
  • Understand filter context

Getting Started with DAX

  • How is DAX different to Excel Formulas
  • Calculated Columns vs Measures
  • Creating a Calculated Column
  • Creating Measures
  • Using Common DAX Functions

Lookup and Filter DAX Functions

  • Why these functions are useful
  • RELATED Function
  • CALCUALTE Function
  • FILTER Function

PivotTables and PivotCharts

  • Creating a PivotTable from the model
  • Working with the model in a PivotTable
  • Filter data using Slicers

Book This Course

Public Course Dates

This course is taught at our training centre in Cambridge.  Check our current available dates for this course below and register your place. Please note that payment is not made by the booking form below – Instead, you will be sent an invoice with instructions on how to pay and confirm your place shortly afterwards.

Company Group Training

We also provide group training for companies on dates that work for them. This can be done via our training centre, on-site or virtually. Please submit your details here for more information.

Online Training

This course is also taught virtually. To enquire about virtual remote training please submit your details here.

Related Courses

Microsoft Excel Introduction

Microsoft Excel Intermediate

Microsoft Excel Advanced Formulae & Functions

Microsoft Excel Advanced Validate & Summarise

Microsoft Excel Dashboards Made Easy

Microsoft Excel Expert Formulae

Microsoft Excel Power Pivot and Power Query

Microsoft Excel VBA