Start a conversation ↗

Description

Power Query in Excel: Importing, Transforming and Automating Your Data

Stop cleaning files by hand and automate data preparation in Excel

  • 2 days — 14 h
  • In-person or virtual
  • Foundation
  • Up to 6 participants

Every month, the same routine: open the export, delete the header rows, split a column, fix the formats and paste the result into the tracking file. This manual preparation takes hours, is repeated identically each period and introduces errors that only surface at review stage.

Over two days, participants learn to record these transformations once and for all with Power Query: connecting to sources, cleansing, transforming, combining files and refreshing in a single operation. Each participant automates one of their own recurring tasks during the session.

Learning objectives

  • Connect to the main data sources from Excel
  • Cleanse and format data in the query editor
  • Combine several tables through merge or append operations
  • Automate the processing of a folder of files sharing the same structure
  • Organise query refresh and resolve reported errors

What makes this programme different

Each participant automates a task they currently perform manually
Applied steps are reviewed one by one to understand what each action produces
A deliberately faulty export is used to build query robustness

Programme

1Connecting and Discovering the Editor

Where data preparation begins

  • Available sources: flat files, workbooks and folders
  • Importing data and previewing before loading
  • Overview of the editor and applied steps
  • Data types and the importance of correct typing
  • Loading to a worksheet or as connection only

2Cleansing and Transforming

Producing a usable table

  • Removing unnecessary rows and columns
  • Splitting and merging columns and replacing values
  • Handling blank values and errors
  • Custom columns and conditional columns
  • Grouping and aggregate calculations

3Combining Several Sources

Assembling without re-keying

  • Appending queries to stack tables of the same structure
  • Merging queries and join types
  • Automated processing of a complete folder of files
  • Managing structural differences between files
  • Pivot and unpivot to restructure a table

4Industrialising Your Queries

A chain that lasts

  • Organising and naming queries
  • Parameterising source file paths
  • Manual refresh and refresh on opening
  • Diagnosing step errors and missing columns
  • Using the result in a PivotTable

Who is it for

Excel users, administrators, support staff, management controllers and anyone who regularly handles data exports.

Prerequisites

Regular use of Excel, in particular tables, sorting and filtering.

Dates & locations

36 scheduled dates between November 2026 and December 2027. Seats are confirmed in the order enquiries are received.

November 2026

December 2026

January 2027

February 2027

March 2027

April 2027

May 2027

June 2027

September 2027

October 2027

November 2027

December 2027

None of these dates suit you? We open additional sessions on request, and any programme can be run privately for your team.

Practical details

Before the programme
Online positioning questionnaire. Your development objectives are shared with the trainer, who tailors the practical case studies to your context.
Teaching methods
Theoretical input, workshops and practical case studies. Digital course materials and method sheets provided.
Assessment
Multiple-choice tests and role-play exercises. Assessment of learning at the start and end of the programme, with immediate and 60-day follow-up evaluations.
After the programme
One year of access to the e-learning platform. Self-assessment of the skills acquired and a 30-day follow-up session with your trainer.
How to register
Registration online or on the basis of a quotation.
Lead time
11 working days after confirmation of registration.
Accessibility
Accessible to people of determination. Contact our accessibility coordinator to design a suitable solution: contact@mpf-academy.ae
Start dates
Rolling intake: in addition to the scheduled sessions, this programme can start on request.