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.

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. http://go.microsoft.com/fwlink/?LinkID=320634
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.
64:10 Links Page
Language Reference: http://go.microsoft.com/fwlink/?LindID=235475
Library Referenece: http://go.microsoft.com/fwlink/?LindID=320634
Office Help: http://go.microsoft.com/fwlink/?LindID=398594

Here is the full Power Query M Language Video

And here is the link to all the BI Stream Content

 

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

Share?

Comments

  1. 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. http://www.apress.com/9781430266914. 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.

  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.

Leave a Reply

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

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x