Excel Power Pivot & Power Query Training Course
Table of Contents
Excel Power Pivot & Power Query Training Course Overview
Our Excel Power Pivot & Power Query training course is designed for experienced users of Excel Pivot Tables wishing to further their knowledge in Excel Power Pivot.
Microsoft Power Pivot is a Microsoft Excel Add-in used for creating Business Intelligence reports based on large data sets inside the familiar environment of Excel Pivot Tables.
Microsoft Power Query allows you to import data from many different sources and then clean, transform and reshape your data as needed. You set up your query once and then reuse it with a simple refresh. Power Query is built into Excel.
Five Key Topics
- Discover Power Query and Power Pivot
- Create the Data Model
- Table Relationships
- Create DAX Measures
- Append and Merge queries
Excel Power Pivot Course Details
Course Duration : 1 day
Course Availability : Monday – Saturday
Course Types : Group booking, 1-2-1 sessions, Bespoke & Public Courses
Training Times : Our courses run run from 09:30 to16:30, course registration 09:00 – 09:30, refreshments available throughout the day. A buffet lunch is available – please discuss with the team.
Facilities : A relaxed environment, air conditioned training rooms, note pads, pens and unlimited access to our online course materials.
Centre and Services
Comfortable & modern facilities
Unlimited access to our online training manuals
Courses delivered by Certified Trainers
Microsoft & CPD courses available
Steve, one of our lead Excelt trainers, has over 25 years of delivering Excel from basics to development levels. All our trainers are encouraged to become Microsoft Accredited within their first year of working at Mouse Training.
Our instructors are very approachable, making the driest subject matter fun.
Excel Power Pivot & Power Query course details
Course Location : Face to face group / public & onsite training available. Virtual training courses available.
Class Size : As a guide we recommend class sizes to be no more than eight.
Virtual / Online training Format : Teams or Zoom.
Tailored Course Content : Our Power Pivot & Power Query courses can be tailored to your requirements – Get In Touch
Tailored Courses : We offer a free consultancy service to help tailor the course to meet your requirements.
On Completion of Excel Power Pivot and Power Query
- Navigate the Power Pivot window
- Specify source data for Power Pivot
- Preparing data for analysis
- Create Power Pivot Relationships
- Enter Power Pivot calculations using DAX
- Create Key Performance Indicators (KPI’s)
- Navigate the Power Query editor
- Create Append and Merge queries
Excel Power Pivot and Power Query Course Outline
- Enabling the Power Pivot Add-in
- Opening the Power Pivot window
- Navigating the Power Pivot window
- Exploring Power Pivot tabs and field list
- Data sources and types supported by Power Pivot workbooks
- Importing data
- Adding tables to a Power Pivot model
- Linking to Excel Tables
- Working with Tables and Columns
- Filtering and Sorting Data
- Creating Table Relationships
- Creating calculations
- Creating and deleting a table
- Renaming a Table or Column
- Setting the Data Type of a Column
- Hiding and Freezing Columns
- Sorting and filtering table data
- Understanding Relationships
- Creating Relationship between Tables
- Viewing and Editing Relationships
- Deleting Relationships
- The Data Analysis Expressions (DAX) language
- Creating formulas for calculated columns and measures
- Using DAX operators
- Review of DAX functions
- Using relationships and lookups in formulas
- Using aggregate functions
- Using filters in aggregate functions
- Recalculating formulas
- Defining a KPI
- Creating the KPI calculation
- Setting the icon style and threshold values
- Editing a KPI
- Discover the Power Query interface
- Getting data from text files and Excel worksheets
- Getting data from databases
- Loading data
- Adding to the Data Model
- Refreshing source data
- Combining data from different files with an file Append query
- Folder Append to combine data files in a specific folder
- Unpivot data to transform traditional spreadsheets into tabular datasets
- Merge matching data from multiple sources without VLOOKUP
- Calculating new columns from existing data
- Convert USA dates to UK dates
Is this Power Pivot & Power Query Course for You?
Give us a call, our admin team will be delighted to assist in making sure you are on the correct course.