FROG Software Training: Excel Power Pivot and Power Query


This course will benefit those who analyse data and create reports. Getting data from different sources and producing dashboards and reports.  

On completion of this course, attendees will be able to use the Power Query and Power Pivot tools of Excel to retrieve, shape, analyse and report on their data.

Attendees must have an advanced working knowledge of Excel.  

Dates Tuesday, February 18, 2020 to Wednesday, February 19, 2020 - Download as vCalendar
Venue FROG, Great Shelford, Cambridge CB22 5NE
Contact Name Rachel Crew
Email rachel.crew@frog.co.uk
Contact Phone 01223 841848
Website Event website

Event details:

What is Power Query & Power Pivot?  

  • 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 

Getting Started with DAX 

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 

For over 30 years we have improved the skillset of over 20,000 individuals with our exceptional and enjoyable courses. Our training and support started back in 1986, when our founder was working with Stephen Hawking as his PA. But you don’t have to be a world-famous theoretical physicist to take part in our training – All our courses from beginner to advanced level provide you with actionable, hands-on training that you can put into practice immediately and save you valuable time!