Intermediate Power Query in Excel
Learn to append and merge queries in Excel Power Query, build conditional columns in M and write custom functions in the Advanced Editor.
Appending and merging datasets, then writing M code, take Power Query in Excel past the basics. The lessons combine datasets by stacking them and by joining them on matching rows, then turn to M, the formula language behind each query step, to build conditional columns, edit code in the Advanced Editor, filter with parameters and write custom functions.
What You Will Achieve
- Stack related datasets with an append and match rows between tables with left outer or inner merges
- Write M expressions with logical operators to add conditional columns, including an early warning score
- Create custom grouped index columns with M table transformation expressions
- Edit and debug query code by hand, reuse built-in M functions and drive filters from parameters
Program Overview
Appending and Merging Queries
Covers the two ways Power Query brings separate datasets together and how the choice of join changes the result.
- When to append and when to merge
- Appending queries to combine datasets
- Left outer and inner joins
Custom Columns with M
Introduces M syntax, which underpins every transformation step, and uses it for custom conditional and grouped index columns.
- M language syntax fundamentals
- Conditional columns with logical operators
- Early warning system score calculation
- Grouped index columns
M Formula Language and the Advanced Editor
Moves into the code view of a query for changes, debugging and reusable logic.
- Modifying and debugging code in the Advanced Editor
- Built-in M functions
- Query grouping and parameters for dynamic filtering
- Custom M functions
Certificate Awarded (Paid)
Job Outlook
Power Query and M skills are used in spreadsheet reporting, data preparation and business intelligence work, wherever data from several tables must be combined and reshaped before analysis.
Good for: Excel users who already shape data in Power Query and now need joins, conditional logic and hand-written M code in their queries.
Keep in mind: Three short chapters: after appends and merges, the M material centers on conditional and grouped index columns, the Advanced Editor, parameters and custom functions.
Bottom line: A focused step into M code that ties appends, merges and conditional columns to editing queries directly in the Advanced Editor.
Frequently Asked Questions
What is M in Power Query?
M is the formula language that sits behind every step a query records. In the lessons you read its syntax, use it for conditional and grouped index columns, call built-in functions and write functions of your own.
Which join types are used when merging queries?
The merge exercises use a left outer join and an inner join. A separate lesson compares appending with merging, so you can tell when to stack tables and when to match rows between them.
What does the Advanced Editor lesson involve?
You open the M code behind a query, change it directly and debug errors in it. The same chapter covers built-in M functions, grouping queries, filtering with parameters and building custom functions.


There are no reviews yet.