Key details

Power Query is a powerful data preparation and ETL (Extract, Transform, Load) tool built into Excel. You can use Power Query to connect data sources, clean and reshape massive datasets, and automate repetitive tasks – all without complex formulas or VBA coding.

In one day, this course teaches everything you need to make the most of Power Query. It is ideal for anyone who wants to:

• Combine data from multiple sources.
• Use Excel formula workarounds.
• Work with large volumes of data.
• Automate parts of your Excel workflow.

Sessions include:

• Common Transformations
• Pivoting and Transposing Data
• Powerful Transformations
• Date and Time Transformations
• Using Power Query with Power Pivot.

Excel Power Query training is arranged on-request, i.e. one-to-one training or a ‘closed course’ for your group. Your training can be tailored to take into account any existing knowledge you have, and the work you’re going to be doing.

Exercises provide activity-based experience, helping you build confidence using Excel. These exercises can be tailored to deal with issues specific to your business.

In-class or online

This course is available in-class at eight centres or live online.

Course times are 9.30am–4.30pm in both formats.

How will I learn?

Excel training is hands-on and practical.

Our trainers teach the necessary theory then demonstrate techniques step-by-step. You then practice the techniques taught on a computer running Excel.

There is plenty of time to ask questions, and you can keep all the files you create.

Expert Microsoft Excel trainers

Microsoft Excel training is hosted by highly experienced instructors. See Expert trainers.

Prerequisites

You should have attended Introduction to Microsoft Excel training training or have equivalent knowledge.

Training guide and certificate

Course delegates receive:

• A comprehensive training guide for Excel, to refer to throughout the course, and to use as a refresher back in the work-place.
• An e-certificate confirming successful completion of a Excel training course.

After course support

Following Excel training, delegates are entitled to 30 days’ email support from their trainer to help with any post-course issues. For further details, see Support.

General information

Payment

Payment for Excel Power Query training can be made by:

• Bank transfer. Please call 01527 834783 for our bank details.
• Card. Visa, Mastercard and American Express cards accepted. Payment can be made securely online or over the phone. Armada doesn’t record or store your card details.
• Buy now pay later. Available for private bookings; pay in 4 x interest-free fortnightly instalments. Facility provided by Clearpay. For details, see Clearpay terms of service.

Purchase orders

We accept purchase orders from UK registered companies with turnover in excess of £1m, and public sector organisations

Accommodation

See Accommodation local to our training centres.

Cancellations and postponements

If an on-request Excel Power Query course booking is cancelled giving less than 20 working days’ notice, a cancellation fee is payable. View cancellation terms.

If an on-request Excel Power Query course booking is postponed giving less than 20 working days’ notice, a postponement fee is payable. View postponement terms.

Terms and conditions

Expert trainers

Gary Crouch

Gary hosts courses in the key Office applications: Excel, Access, Outlook, PowerPoint and Word.

He has been training people in spreadsheets since 1988, making him one of the most experienced Excel trainers in the field today. With a BEng in Mechanical Engineering and an MSc in Computer Science, he brings a rare combination of technical rigour and engineering pragmatism to his courses, helping delegates understand not just how to use features, but why they work the way they do.

Gary is known for making training accessible and directly relevant to the job at hand. His long track record and cross-industry experience makes him a trusted choice for organisations looking to their teams’ Microsoft Office knowledge and capability.

Course content

Introduction to Power Query

• Power Query Editor and basic transformations
• Quick insights, data quality and distribution
• Connecting to multiple data sources
• Loading data from sources such as SharePoint, CSV, the Web and email
• Refreshing data

Common Transformations

• Splitting columns (delimiter, character count, position, digit to non-digit, etc.)
• Formatting columns (UPPERCASE, lowercase, Capitalize Each Word, Trim, Clean, etc.)
• Statistical functions (Sum, Min, Max, Median, Average, Standard Deviation, Count Distinct)
• Replacing values
• Rounding data
• Filtering columns (number and text filters)
• Spotting duplicates between two columns
• Understanding data types and why they matter
• Filling and replacing values
• Finding and correcting errors in data

Pivoting and Transposing Data

• Pivoting and unpivoting columns
• Transposing columns

Powerful Transformations

• Conditional columns (equivalent to IF / nested IF, but easier to amend)
• Merging data with another file (more efficient than VLOOKUP)
• Column from Example (an enhanced version of Flash Fill)
• Appending data between different files
• Combining all files in a folder
• Extracting text based on a pattern
• Fuzzy matching
• Group By — grouping data by a chosen factor
• Power Query navigation tips
• Finding and correcting errors in data
• Creating custom columns
• Excel’s Data Model — combining sources for bespoke Pivot Tables

Date and Time Transformations

• Introduction to Date and time transformations
• Building calendars
• Calculating age
• Time/date differences
• Durations

Using Power Query with Power Pivot

• Loading queries into the Data Model instead of worksheets
• Why Power Pivot? Moving beyond a single flat table
• Creating relationships between multiple queries
• Building measures with DAX (e.g. SUM, AVERAGE, CALCULATE)
• Calculated columns vs. measures — when to use each
• Time intelligence (year-to-date, prior period comparisons)
• Building Pivot Tables from the Data Model
• Refreshing the full Query-to-Model pipeline

Live online training

Online training lets you participate in a live Excel course from your place of work or home.

We do everything possible to make your online training experience as close as possible to actually being in the classroom. You can:

• See and hear our trainer, and the other delegates participating in the course…
  
• Watch our trainer demonstrate techniques in Excel.
• Interact by speaking, using chat, or raising your ‘virtual hand’ if you need assistance.
• Share your screen with our trainer, and allow them to take control to explain something.

Like our in-class courses, online Excel Power Query courses…

• Are presented by expert instructors.
• Give you plenty of opportunity to carry out assisted, practical exercises using Excel.
• Offer 30 days’ email support from your trainer.

All you need is an Internet-connected computer with…

• Excel installed.
• A webcam and audio capability. If your computer running Excel doesn’t have these, you can use a second device, such as another computer or tablet. See Hardware recommendations for live online training for further details.

If there’s anything you’re not sure about we’ll do our best to help you. We won’t, however, be able to assist with your Excel installation.

We provide everything else…

• Links to download the files, resources, etc. you’ll use in your course.
• A training guide. For most courses, you can choose from a printed copy that we post to you, or a PDF.

In the week before your course, we’ll send you an email containing everything you need to prepare for and participate in the training. We’re available in the days ahead of your course to assist if you need any help.

Please note that we are not able to record scheduled online courses for privacy reasons.

When you attend live online training, you’re participating in a real course, hosted by a real trainer, in real time