Excel Power Query Training Course
Table of Contents
Excel Power Query Training Course Overview
Our Excel Power Query training course is ideal for you if you have to spend time every month cleaning and transforming data before you can even begin to analyse it and incorporate it into your reports.
We show you how to import data from many different sources and then combine, transform and reshape your data as required for your Pivot Tables and other Excel reports. You set up your query once and then reuse it with a simple refresh. Power Query is built into Excel.
Five Key Topics
- Creating the Data Model
- Calculating new columns
- Unpivoting data
- Append and Merge queries
- Using the M Language
Excel Power Query 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.
Excel 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 Introduction 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.
Centre and Services
Comfortable & modern facilities
Unlimited access to our online training manuals
Complimentary lunch
Courses delivered by Certified Trainers
Microsoft & CPD courses available
Our Trainers
Steve, one of our lead Excel 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.
On Completion of Excel Power Query
- Navigate the Power Query editor
- Loading data into Power Query
- Merging and splitting data
- Transforming data
- Creating new columns
- Create Append and Merge queries
- Understand the different types of table joins
- Refreshing data and the Data Model
- Introduce the M Language for data manipulation
Excel Power Query Course Outline
- Applied Steps
- Data Types
- Blank cells, Nulls and Zeros
- The M Language (M for formula!)
- Importing data from text files and Excel worksheets
- Importing data from databases
- Loading data
- Adding to the Data Model
- Fill Up And Fill Down
- Splitting Columns by Delimiters
- Splitting into Rows
- Merging Columns
- Filtering Rows
- Removing duplicates and errors
- Sorting
- Calculating new columns from existing data
- Adding Conditional Columns
- Multiple Conditions
- Adding a Column using Column from Examples
- Adding Custom Columns with Power Query Functions
- Performing Calculations with Grouping
- Transpose data
- Unpivot data to transform traditional spreadsheets into tabular datasets
- Convert USA dates to UK dates
- Duplicating and referencing other Queries
- Joins and Merging
- Merge matching data from multiple sources without VLOOKUP
- Defining the main types of Join
- Full Outer Joins and Inner Joins
- Right and Left Anti Joins
- Left and Right Outer Joins
- Combining data from different files with an file Append query
- Folder Append to combine data files in a specific folder
- Refreshing your Queries
- Options for loading to Excel
- Loading to the Data Model or Power Pivot
- Load Settings and Automatic Refresh
- Where to edit and write M
- Using an M function to add a Custom Column
- Looking up M functions
- Create a function from a query
- Create a Custom Function
- Referencing other Queries in M Code
- Commenting your code
- Error Trapping
- Creating a Parameter
- Inserting parameters into M formulas
- Create a List from a Table
- Create parameters from Excel worksheet cell values
- Dynamic file and folder paths
- Storing End Dates and Cost Centres for reports
Is this Course for You?
Give us a call, our admin team will be delighted to assist in making sure you are on the correct course.
