Power Query for Automation and Data Preparation

In-Person Practice-Oriented Training (Vienna)

This two-day beginner-to-intermediate Power Query Training will teach you how to clean, transform, and reshape data using Power Query (the powerful ETL engine in Excel and Power BI). You will explore core concepts through numerous practical exercises, ensuring you can apply the skills in your daily work.

  • Duration: 2 days × 4 hours = 8 hours total
  • Language: English
  • Format: In-person classroom training in Vienna

Learning Objectives:

By the end of the course, you will know how to extract, clean and reshape data efficiently, so the resulting tables can be loaded into Power BI or Excel.

power query logo

Prerequisites:

Laptop with installed Power-BI Desktop (free download here)
Experience with Excel or Power BI (check our Power BI courses)

Price per Person:

€390,- (+ VAT)

Learning Topics:

  1. Why Power Query
    • Introduction to Power Query: What it is, why use it
    • Accessing Power Query in Excel and Power BI
    • The Power Query Editor interface: Ribbons, Applied Steps pane, Query Settings, Formula bar, Advanced Editor preview
  1. Extracting Data
    • Connecting to data sources: Excel files, CSV/Text, folders (multiple files), databases (SQL, Access), web pages, SharePoint, etc.
    • Basic data loading & preview: Navigation, choosing tables/sheets, Promote headers, data type detection
    • Refreshing queries & maintaining connections (including changing source paths)
  1. Core Data Transformation & Cleaning
    • Filtering rows (basic + advanced / text filters / number filters / date filters)
    • Removing / keeping duplicates
    • Sorting data
    • Removing / choosing / reordering columns
    • Changing data types & locale-aware transformations
    • Replacing values (simple + advanced patterns)
    • Filling down / up (fill values), replacing nulls
    • Splitting columns (by delimiter, number of characters, positions, lower/upper case, etc.)
    • Merging / combining columns (concatenation with custom separators)
    • Extracting text (left/middle/right, before/after delimiter, length)
    • Formatting text (trim, clean, upper/lower/proper, remove non-printable characters)
  1. Combining Data
    • Appending queries (stacking similar tables / files from folder)
    • Merging queries (joins: left outer, inner, full, anti joins — equivalent to VLOOKUP / INDEX-MATCH / SQL JOIN)
    • Fuzzy matching options during merge
  1. Date & Time + Numeric Transformations
    • Date & time transformations: Extract year/quarter/month/day/weekday, age calculation, end/start of month/quarter/year
    • Creating fiscal/calendar periods
    • Duration / elapsed time calculations
    • Adding date/time from number or text
  1. Conditional & Calculated Columns
    • Adding custom columns (basic formulas)
    • Conditional Column dialog (if-then-else logic)
    • Custom Column with M language basics (simple expressions)
  1. Reshaping Data
    • Unpivoting columns (turning wide data into tall/long format)
    • Pivoting columns (less common but sometimes included)
    • Transposing tables
  1. Practical & Intermediate Topics
    • Grouping & aggregating data (group by with sum/count/average/min-max + multiple aggregations)
    • Parameters (simple dynamic filtering / file paths / values)
    • Error handling: Removing / replacing errors, Try…otherwise patterns
    • Best practices: Naming queries, disabling load, query dependencies, avoiding common pitfalls
    • Performance optimization & query diagnostics
Pawel Jaszczynski - Power BI Developer and Trainer

Trainer / Organizer:

Pawel Jaszczynski, MCT, PL-300
EXCELLAB
Erzherzog-Karl-Straße 221/10, 1220 Wien-AT
info@excellab.at