Power Query: the fundamentals
Automate imports and clean-ups in Excel and Power BI: queries, types, merges, groupings and combining files.
Access: an e-mail address is enough, you receive a sign-in code. Free modules need no bank card.
What you will be able to do
- Understand the Power Query, Power Pivot and Power BI ecosystem and the ETL principle
- Load, clean and refresh data: headers, types, calculated columns
- Import and combine the sheets of a workbook and the files in a folder
- Rename, filter, group, unpivot and merge queries instead of using VLOOKUP
- Master joins, anti-joins, dynamic splits, subtotals and productivity shortcuts
- Consolidate a folder of files with a query that withstands changes
Who it is for
Excel or Power BI users who prepare data by hand (exports, monthly files, consolidations).
Prerequisites
Excel basics (tables, simple formulas). No knowledge of Power Query or the M language is needed.
Included
- Video with chapters
- PDF slides
- Marked quiz
- Verifiable certificate
Programme
Fundamentals
- 01Understanding the Power Query ecosystem (Excel and Power BI)
- 02First ETL pipeline: load, clean, refresh
- 03Interface, combining tabs and load modes
- 04Headers, data types and calculated columns
- 05Importing and combining a folder of Excel files
- 06Renaming and filtering your data
- 07Grouping and unpivoting your data
- 08Merging queries to replace VLOOKUP
Intermediate
- 09Five key techniques: combine, unpivot, fill, merge
- 10Ten productivity tips in Power Query
- 11Joins: merge types and anti joins
- 12Grouping rows and splitting a column dynamically
- 13Subtotals and a grand total in Power Query
- 14Project: consolidating a folder and withstanding changes
Training a team? Access for several employees, on quotation. admin@axent-consulting.com