Microsoft Power Query – data analysis using the M language and optimising the data analysis process

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Efficient data import - You will learn how to pull data from Excel, CSV, JSON, XML, folders, and online sources, so you can build a reliable input layer for your reports on your own.

Better data preparation - You will master data cleaning: removing unnecessary rows, fixing headers, setting correct data types, and handling duplicates and blanks to deliver consistent datasets for analysis.

Practical transformations - You will learn to split and merge columns, use pivot and unpivot, create calculated columns, and apply conditional rules to turn raw inputs into tables ready for reporting.

Combining multiple sources - You will learn how to join and append tables correctly, handle unmatched records, and prepare source structures so your data combinations work predictably and cleanly.

Reporting automation - You will build queries ready for recurring refreshes, use parameters and dynamic folders, and reduce manual fixes whenever new files or updated data arrive.

Faster, more stable queries - You will understand what affects Power Query performance, how to arrange steps in the right order, and how to diagnose issues that slow down refreshes or break data loads.

Confident use of M - You will understand let/in syntax and how steps are written in M, so you can edit Power Query code with confidence and handle transformations that the interface alone does not cover.

Report-ready query design - You will learn to design queries for further analysis in Excel or Power BI, so your datasets stay clear, consistent, and ready for trustworthy reporting and reuse.

Training programme

1. Introduction to Power Query

the role of Power Query in the data preparation process (ETL),
Power Query in Excel vs Power BI – scope of capabilities,
interface: query editor, Applied Steps, data preview,
principles of designing queries for subsequent reporting.

2. Data sources and import

import from files: Excel, CSV, TXT, JSON, XML,
import from a folder – consolidation of multiple files,
connections to databases and online sources,
connection settings, credentials, data privacy.

3. Data cleaning and preparation

removing unnecessary columns and rows,
working with headers, technical rows, footers,
standardizing data types (dates, numbers, text),
removing duplicates and empty values,
standardization of column names and dictionary values.

4. Data transformations

operations on columns: splitting, merging, extracting text fragments,
changing the data format: pivot and unpivot,
grouping and aggregations (Group By),
filtering, sorting, creating calculated columns,
logical conditions and data transformation rules,
replacing values and mapping categories.

5. Merging and appending data

Merge – merging tables (joins: left, right, inner, full),
Append – appending data (union) from multiple sources,
handling missing matches and data conflicts,
preparing data structures for correct merging.

6. Automation and scalability of solutions

data refresh – manual and automatic,
working with dynamic folders and changing file structure,
query parameters (e.g. date range, file location),
preparing queries for cyclical reporting.

7. Performance and stability of queries

the order of steps and refresh time,
limiting data volumes already at the import stage,
diagnostics of slow queries,
the most common problems during data refresh.

8. The M Language in Power Query – practical context

M language syntax: let/in, steps, code readability,
where M code appears in Power Query and how to modify it consciously,
simple examples of editing M for unusual transformations.

What are the prerequisites for participating in the training?

Basic Excel skills - You should be comfortable working in Excel: opening files, moving across worksheets, filtering data, and understanding table structure, as many exercises will rely on that.

Working with data tables - You should understand columns, rows, headers, and common data quality issues so you can move smoothly through exercises focused on cleaning and organizing datasets.

Basic data analysis - You should have some basic experience with data analysis, such as sorting, filtering, and simple grouping, to understand the purpose behind the transformations more easily.

Initial Power Query exposure - It is worth having basic familiarity with Power Query or at least having seen the query editor, because this course focuses on intermediate work rather than a full introduction.