Deep Dive into Power Query M Language - Excelerator BI

Deep Dive into Power Query M Language

Matt's curated best self serve BI content

In this session, Matt Masson and Tess Palmer do a great job of showing everyone the depth and power of the Power Query M language that is the backbone of Power Query – it makes my list. Clearly they didn’t expect too many people in the audience to be able to follow them along the way, which came out in a light sarcastic way at times, but I think this reflects the state of maturity of this tool. Not too many people have deep knowledge since it is so new. I am waiting for Chris Webb to release his new book so I can get in some late night reading. Here is the agenda – mainly demos which is good.

Power Query Online Training

M language agenda

and here is the relevant content

Time Stamp Description
0:00 Intros
1:55 Demo/Overview of the Power Query product, tab etc. Not too much new here.
2:50 A good lesson about the SQL Statement window. Don’t use it.
3:15 A great 2 min overview of what Power Query does, how the formula bar works, how to access the advanced editor, and good advice to use the UI first, then edit formulae.
5:00 Intro to M Language, why Microsoft selected it, why DAX is not up to the job.
7:30 Introduction to the concept of “Query Folding” – how Power Query pushes the work back to the database when it can. Good to know.
12:15 Some information about Library Functions. Here is a link with a PDF download that has a formula library reference that they talk to on this slide.
13:30 Demo time.

  • Explains the new features to change the default load behaviour to Excel and/or data model.
  • How to get inline help for the M language
  • Shows making changes to the advanced editor to bring in a 3 way join of tables from a DB.
17:45 Language Flow explanation – how the language passes on transformations from one step to another.
19:45 Type System. Explains the different data types, and how to define them, with a demo
29:30 Demo: Import and combine multiple CSV files into one table
32:40 Demo: Import a single file (Excel), edit the query to create a function, then import multiple versions of the same file and combine them. Matt shows you how to edit the regular query to a function.
41:30 Demo: Download complex data from Web. Good example of the breadth of the language using functions to download data.
48:15 Demo: Another web demo downloading multiple pages using a function.
55:40 An example to extract data from Binary objects. Far too deep for me!
61:00 Tips and Tricks. Worth watching. #shared in the formula bar will give you a list of functions and keywords.

Here is the full Power Query M Language Video

And here is the link to all the BI Stream Content

The ‘M’ Language reference is now available at

If you want a comprehensive l on how to use Power Query, checkout my training course here

5 thoughts on “Deep Dive into Power Query M Language”

  1. Great Matt, i will wait till you post that link up….also Is M is for Data Monkey being updated would you know..looks like i need to get a few books for tax year end!

  2. Thank’s for sharing the news on Chris’s book. I can’t wait for this to be released, and I am hoping to be able to sell the book in Australia from my online book store.

  3. I bought an Alpha copy of Chris Webb’s ebook from Aoress for $28 this week. Looks like all the content is there before final editing and typography. Pretty good so far. Nice overview of Power BI and how Power Query fit in. He then shows how to use the GUI, has a chapter on M, chapter on Data Catalog, then a final chapter with detailed examples to learn from.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top