MSPTDA 09 Power Query Complete M Code Introduction: Values, let, Lookup, Functions, Parameters, More

Download Excel START Files:
Assigned Homework - these are problems for you to practice your new M Code skills:

In this Video learn the basics of M Code, the computer language behind queries in Power Query.
Topics:
1. (00:15) Introduction
2. (03:46) Edit M Code: Applied Steps
3. (03:46) Edit M Code: Formula Bar
4. (03:46) Edit M Code: Advanced Editor
5. (09:50) Expressions
6. (09:50) let expressions
7. (17:34) Comments in M Code
8. (21:11) Values: Primitive, List, Record, Table, Function
9. (30:45) Lookup or Projection and Selection. Learn about Row Index Lookup and Key Match lookup
10. (42:50) Primary Keys, including Table.AddKey function.
11. (50:20) Custom Functions
12. (57:44) Parameter Queries
13. (01:02:27) Underscore Character _
14. (01:06:17) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

[excelisfun]

Here is a followup video that shows how to deal with Formula.Firewall Error after we use a parameter query to deliver the folder path to the query created at 01:00:45 in this video:

[ElTigreFIME]

Mike, you deserve heaven for all the help provided to this community of enthusiast learners….God bless you!

[arifzainal]

When I hear "Wow, that's a lot of fun..", I immediately knew I absolutely need to re-watch this again :D. Packed with real gems!! Thanks very much Professor Mike!

[chhanditachowdhury]

I have never seen somebody put in so much effort to teach stuffs. Wishing you many more subscribers.

[anisdadani]

This series is price-less. I have subscribed to at least 20 paid courses on Udemy. However none can beat you in transferring knowledge. May Allah ( SWT) bless you with best

[greghorsman]

Hi Mike, I can see from the date and time stamp on most of the comments that I am late to the game. Still, I am extremely satisfied that I found your channel. Your presentation and teaching skills are definitely top shelf and I can say that I've been working with PQ for the last 2 1/2 years. I look back on my early work and just shake my head and wonder how any of it worked. Thanks for making great informative videos.

[imatzav]

I'm speechless once again! Yesterday it took me several hours looking here and there to figure out how to do exactly the same thing as your example starting at 57:50. I wish I had watched this video earlier. You are the best Mike! I've learned so much from your videos all these years.

[tosaksettharungson]

After many self trials and errors expecting M to be kind of similar to VBA and Excel formula, this episode came to the rescue and gave me hope again. THANK YOU!

[maci]

Working a couple of years with Power BI now. Never quite liked the M side of it. I managed to do the basics without actually understanding M-code. This video changed that, BIG TIME! Thanks a lot!

Now heading back to my EMMM CODE (lol!)

[NjaanAdima]

Your presentation is more powerful than the power query! Feeding energy to the brain. I started loving the data analysis after watching a couple of your YouTube. Thank you very much, Mike! Keep Safe.

[panh]

It took me at least 4 times to go back this videos to nearly understand the whole amazing thing! Gonna spend the 5th time :) Thank you sir!

[ljubicar]

WOW!!! This video is worth watching dozens of times! Can't find the words to complement your classes enough. Thank you for unselfishly sharing your knowledge!

[mazarata]

Wow, just speachless on the information density packed in this video and on how well founded it is. That's really an incredibly steep learning curve. For me as a trainer I get inspired by your didactical path, Mike. Am very much looking forward to your next videos. Thanks so much.

[harifitfunk]

This is fabulous given the simple way you put forth every minute detail in a large video. The articulation is really awesome. I watched for 3 times to get nitty gritty of the CLASSIC CLASS

[ziggle]

First-rate exposition. Best presentation I have seen on the data structures in PQ and the M-syntax needed to access the data within them. Thanks, Mike!

[malchicken]

One of the most informative videos I have watched in years of watching YouTube videos. Very well done, thank you.

[larrycroce]

Mike, I've watched your entire series on Array Formulas and am working my way through these Power Pivot videos. Your videos are by far the best series I've ever watched. The things I've learned have been a great benefit in my overall Excel skills as well as having real work applications. I have multiple data files located across multiple sites and I've been able to link them all into a very useful dashboard thanks to you. Keep up the great work, you're providing a terrific service for us!

[masterof]

Thank you Mike - this one is amazing! I learn so much of PQ in this video, I wish I have watched it earlier! Many thanks for your great efforts to make these videos, many things in Power Query starts to make sense to me after watching this one. I found myself getting lazier nowadays, instead of writing those great formulas (especially ones involves array formulas and many complex and long formulas which no one else can read) I am now using Power Query to solve. I can make a copy of the file, delete all the power queries to deliver just the results, and no one knows how I make the magic happen! Now I use the time saved to watch even more ExcelIsFun videos!

[damienmearns]

You show one thing, and I'm asking "yeh but what if..." and the next sentence you answer it - time and time again - top pro ! best video on M I've seen

[kamranb]

This was the best video I have ever seen on M code. Thanks a lot, Mike. I am gonna watch it a couple of times, there is a lot of info. Have to master everything because this video covers all the foundations.