DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)
Download files: https://excelisfun.net/files/DAMEwithMPT05.zip, pdf notes: https://excelisfun.net/files/05-DAMEMPT.pdf
Alternative download links: Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week08/DAMEwithMPT05.zip
Pdf notes to read online: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week08/05-DAMEMPT.pdf
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