DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)

DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)
In this video learn about all the fundamentals of the M Code language, the coding language behind Power Query. Learn all about the keys to M Code Mastery: M Code Values, Expressions, Data Types, Operations by Data Types, let expression, M Code Lookup, Custom Functions, and M Code functions such as: Table.AddColumn, Csv.Documnet, Excel.CurrectWorkbook, Table.Group and much more!
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
Topics:
1. (00:00) Introduction
2. (00:32) Why M Code?
3. (02:32) Files to download and follow along
4. (03:20) Power Query Editor)
5. (04:00) 3 Places to edit M Code
6. (04:28) Introduction to let expression
7. (07:02) Define Expressions
8. (07:45) Introduction to the 15 M Code Values
9. (10:19) Data Types, Type value
10. (10:52) Operations and Data Types
11. (12:25) identify Expressions in a let expressions
12. (13:46) Change Data Type
13. (14:15) Group By and Table.Group function, first example. Why list within a list is so useful!
14. (16:00) Identifiers in M Code and why you never use spaces
15. (17:43) Hack Group By dialog box to make calculations not in dialog box
16. (19:10) Keywords
17. (19:50) Editing in Advanced Editor, including Shift + Enter
18. (20:30 Syntax for let expression
19. (21:38) All 15 M Code Values and Operators that are allowed for each M Code Value
20. (22:29) Null value
21. (23:48) Logical value and formulas
22. (24:28) Text value and formulas
23. (25:22) Number value and formulas
24. (25:52) Why it is important to use value type and not data type for determining whether an operation is valid.
25. (26:50) Relationship between Values and Data Types
26. (27:57) Colaesce operator or if expression when you have null values?
27. (30:20) Custom Column and Table.AddColumn function
28. (31:25) Time value and formulas
29. (32:46) Date value and formulas
30. (33:34) Date.AddDays function
31. (33:59) Duration value
32. (34:12) Duration.Days function
33. (34:31) Power Query Dates (1/1/0001 to 12/31/999) and how they Rule: many examples!!!
34. (38:41) Calculate hours worked through midnight. This is basis for custom function later in video
35. (40:23) Number.Round function vs. ROUNDDOWN vs. INT
36. (40:58) let expression to define variables in formulas
37. (43:26) Convert ISO Dates to serial number dates
38. (44:29) Using Locale feature: Convert dates and numbers from one locale (France) to another (United Sates)
39. (46:24) Duration.Days vs. Duration.TotalDays functions
40. (47:00) Datetime value and Datetimezone value
41. (47:44) Table, list, record values can hold more than one M Code value
42. (48:00) List value and formulas
43. (50:21) Aggregate functions require lists
44. (51:24) List to expand rows from improper data set with a range of years in cells
45. (54:12) Record value and formulas
46. (54:31) Generalized Identifiers
47. (55:14) Table value and formulas
48. (56:26) Binary value
49. (56:43) M Code lookup
50. (59:32) Row Index Lookup examples
51. (01:01:26) Key Match Lookup examples
52. (01:03:32) Excel.CurrectWorkbook function
53. (01:04:38) Primary Keys and lookup
54. (01:06:46) Lookup columns for aggregate functions
55. (01:07:43) Merge feature and Join Operations: Left Outer, Inner, and Left-Anti
56. (01:12:35) Function value: custom functions
57. (01:13:58) Hours worked custom function
58. (01:19:00) On Premine folder and file paths and Data Connections dialog box
59. (01:20:17) Fix and Append Text Files custom function
60. (01:25:00) Append tables with Table.ExpandColumns function
61. (01:25:37) Append tables with Table.Combine function
62. (01:26:30) each and underscore explained!
63. (01:32:30) Approximate Match custom function
64. (01:39:55) Table.Group function fourth argument: GroupKind
65. (01:42:40) Table.Group function fifth argument: Comparer as function
66. (01:48:05) Summary
67. (01:49:45) Conclusion

#mcode #powerquery #powerbi #powerbidesktop

[excelisfun]

I have posted other free 2 hour M Code classes before, but this is the latest and best : )

[Vivian-xk]

I started watching your videos back in 2016, and I can't express how much I've learned from you over the years. Your teaching style is clear, engaging. You're the best teacher I've ever had, and I feel incredibly grateful for all the knowledge you've shared.

[SalmanShaikh-zgqn]

You are a master, the way you covers the topic was awareness

[reng]

THE BEST EXCEL TEACHER EVER!!!!I I WILL SEE THE FULL VIDEO X LATER, BUT A BIG THANKS FOR YOUR CONTRIBUTION AMIGO!!! SLDS FROM MONTEVIDEO!

[PieterdeWit-gwpw]

ExcelIsFun by far the best Excel tutor!

[unionafrican]

43 year old and still following my teacher since 2010 maybe ❤

[KumarAnalytic]

In a folder I've so many Excel files and want to load into power query in one go as different data like Sales, Payroll, Attandance, Returns, Product etc. here I just want to load and I don't want to combine it.

Thanks a lot ExcelisFun!!

[gvitullib]

A week after I started watching this MasterClass, I finally finished it. Why a week? because I do all the exercises, step by step, I study the notes in pdf and I go back to see any chapter I need to understand the concept. Kudos Mike, and thank you.

[richardhay]

Definitely the best. Replaces all others by ALL creators. But why? The first time I opened Power Query it was like opening a box of Xmas ornaments with a massive array of sizes shapes and colors which caused me to ask WHERE'S THE TREE?? This video provides the tree and in conjunction, with the PDF notes, the schematic on where and how to hang them. Simply put No instructiins--No bueno! I rarely have a two hour attention span for a lecture or any movie/video (which is why I have been to a theater exactly zero times since I subscribed to Netflix) but this video was an uber-rare exception!! Good

[timmybone]

Thanks Mike! I also bought your new book, but I got my work to pay for it cuz we're all gonna use it at the office. (Small BI team of 5)

[rtrbs]

Thank you very much Finished today but will be seen this Video many more times.PDF is very awesome

[chrism]

Yes! Sunday-funday with M Code. Let's roll Team! Thanks MIke

[mcwahaab]

You have an exceptional teaching method and M is the topic I enjoy the most in PQ. I appreciate your work, Mike.

[azizurrehman]

This is really epic one, i am also following since from 2, 3 years especially when MS 365 comes into the Court

[chrism]

I just watched it all the way through, excellent Mike!

[GeertDelmulle]

OMG, Mike! What a treat! 🙂
You know I can't pass this up – I have to watch it! 🙂
Just finished another book review (don't tell Mr. Excel just yet! ;-) and now you drop this?!
No reprieve, rocking on hard!...😅

[kebincui]

Excellent as always. Thank you Mike and looking forward to buying your new book.

[rrrprogram]

Mikee.... How u doing.... Believe me... Last one week i was trying to master m language.... I was thinking if mike would make new video on m language....

Thanks a tonn mike...

[sanjeevsoni]

Using Comparer function is excellent in the last example

[kiwikiow]

This epic video is amazingly good. Thank you very much for making this, Mike 💚