Demystifying M in Power Query
The step-by-step course to finally understand, edit, and write M code.
If clicking buttons can’t fix your data reshaping problems…
M can!
You rely on Power Query to clean, shape, and automate your data. But what happens the moment you need something beyond clicking buttons? You’re stuck.
❌ The code looks like a mix of random special characters thrown together.
❌ There’s strange words like let, each, type and null.
❌ Cryptic error messages appear that you have no idea how to fix.
You know Power Query can do more.
But without understanding M code, you hit a wall, again and again.
I get it. M is intimidating the first time you see it.
Power Query’s interface shows you tables first. But the M code behind tables actually relies on concepts like lists, records, types, and functions. Things you haven’t learned yet.
So, you’re thrown into intermediate concepts before you even understand the basics.
It’s like starting a book in the middle, so it’s no wonder you’re confused.
But here’s the truth:
M code isn’t difficult, you just didn’t start at the beginning.
And once you understand how the language works, everything will click into place.
So, we created Demystifying M in Power Query. It’s the the complete step-by-step course designed to turn confusion into clarity.
Watch the video
Learn M without feeling overwhelmed
If you’re concerned that you’re not a “coder”. Don’t worry, M is a function based language just like Excel.
The course will take you from “What even is M?” to confidently writing, editing, and troubleshooting your own code.
STEP #1
Build foundations
Learn the structure and syntax
of the M language.
STEP #2
Learn by doing
Follow along with examples,
writing all the code yourself.
STEP #3
Master the language
Understand lists, records, tables,
functions, metadata and more.
The Demystifying M in Power Query course is only available within the Excel Academy.
What’s included?
✓ 12+ hours of on-demand video lessons
✓ Examples to solve common Power Query scenarios
✓ Exercises and quizzes
✓ Certificate of completion
✓ English closed captions
✓ Ask questions and get expert answers
Syllabus:
Introduction
- Welcome & learning journey
- About the exercises
- Download the support files
Getting started with M
- Getting started with M – Introduction
- What is M?
- Writing and editing M
- Editing step names
- Introduction to errors
- Introduction to values
- Operators
- Examples: Values & operators
- let and in statements
- Lazy evaluation engine
- Identifiers
- Nested let & in statements
- Introduction to functions
- Special function values: Enumerations and culture codes
- If statements
- Example: Identifiers, functions & if statements
- Comments
- Code colors
- Keywords
- User Interface Tips & Tricks
- Getting started with M – Quiz
Primitive values
- Primitive values – Introduction
- Null values
- Text values
- Text functions
- Number values
- Accuracy and rounding
- Number functions
- Logical values
- Date & time related values
- Date and time related functions
- Exercises – Primitive values.
- Primitive Values – Quiz
Lists
- List values – Introduction
- List values
- Range operator
- Accessing list values
- List functions
- Lists and tables
- Exercises: List values
- Example: Get value from a cell
- Example: Get variance to previous row
- Example: Calendar tables
- Example: Get column names
- Example: Rename column by position
- Example: Dynamic column expansion
- Example: Remove null columns
- Exercises: List scenarios
- List values – Quiz
Records
- Record values – Introduction
- Record values
- Accessing record values
- Record functions
- Records and let & in statements
- Records, lists and tables
- Exercises: Record values
- Example: Get value from a cell – Revisited
- Example: Reference intermediary steps
- Example: Calendar table – Revisited
- Example: Calculate with records
- Example: Unpivot paired columns
- Example: Unstack to columns
- Example: Realign values
- Exercises: Record scenarios
- Record values – Quiz
Errors
- Errors – Introduction
- Error values
- try and otherwise
- Error handling
- Errors – Quiz
Tables
- Table values – Introduction
- Table values
- Accessing table values
- Understanding documentation
- Table functions
- Exercises: Table values
- Example: Group kind
- Example: Missing field ignore
- Example: Expanding structured values
- Example: Combine vs expand
- Example: Add list to a table
- Example: Multiple header rows
- Exercises: Table scenarios
- Table values – Quiz
Types
- Type values – Introduction
- Type values
- Value type vs column type
- Type ascription
- Facets
- Ascription vs conversion
- Example: Convert data types
- Example: Filter by data type
- Example: Calendar table – Revisited Pt2
- Exercises: Type values
- Type values – Quiz
Connectors and binary
- Connectors – Introduction
- Connectors
- Binary values
Functions
- Function values – Introduction
- Function values
- Nullable and optional arguments
- Scoped functions
- Example: Financial Year Quarter Month
- Example: Trim text
- Each and _
- Converting queries into functions
- Copying functions between workbooks
- Exercises: Functions
- Functions – Quiz
Combiners, comparers, replacers and splitters
- Combiners, comparers, replacers and splitters – Introduction
- Comparer functions
- Replacer functions
- Combiner functions
- Splitter functions
- Exercises: Combiners, comparers, replacers and splitters
- Combiners, comparers, replacers and splitters – Quiz
Iteration
- Iteration – Introduction
- Iteration functions
- Using Table.AddColumn
- Using Table.ReplaceValue
- Using Table.TransformColumns
- Using Table.SelectRows
- Using List.Transform
- Using List.Accumulate
- Using List.Generate
- Example: Transform nested tables
- Example: Find and replace
- Example: Running total
- Iteration – Quiz
Metadata
- Metadata – Introduction
- Metadata records
- Example: Reference intermediary steps – Revisited
- Special metadata fields
- Metadata – Quiz
Optimization
- Optimization – Introduction
- Query optimization techniques
- Timing refresh
- Faster connectors and optimized inputs
- Query folding
- Privacy levels and formula firewall
- Buffering
- Optimization – Quiz
Wrap up
- Wrap-up
- Feedback
- Certificate
Your path to success is as simple as 1-2-3.
Join the Excel Academy
Enrol today to get instant access to the Demystifying M in Power Query course.
Follow the course
Discover how M is constructed, understand the syntax, and solve common real-world problems.
Apply the learning
Use your new skills to edit and write M code and save even more time.
The Demystifying M in Power Query course is only available within the Excel Academy.
How Demystifying M in Power Query will help you?
With the course:
✔️ Read and understand any step created by the UI
✔ Fix broken queries without guessing
✔ Write your own functions to automate repetitive tasks
✔ Create smarter and faster queries the UI can’t build
✔ Handle errors cleanly
✔ Optimize queries
✔ Feel in control instead of confused
Once you understand M, Power Query changes from a black box to a controllable superpower.
Without the course:
❌ Always be restricted to what the UI allows
❌ Simple changes will continue to waste hours
❌ Never be able to debug complex queries
❌ Can’t create reusable logic
❌ Data processes remain fragile
Power Query is one of the most valuable Excel skills today. But until you know the language behind it, you will never master it.
Learn M and unlock the full power of Power Query.
The Demystifying M in Power Query course is only available within the Excel Academy.