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.
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.
|
| 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 https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference
If you want a comprehensive lhttps://exceleratorbi.com.au/blog/teched-2014/esson on how to use Power Query, checkout my training course here
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!
I don’t think M is for Data Monkey will be updated until later this year.
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.
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.
You are supposed to have a disclaimer if you have spoilers 🙂
Thanks for the link(s)!