Dive deep into the fundamentals of Power Query M Code in this comprehensive 1 hour 50 minute video tutorial. Master the essentials of data shaping and analysis by exploring M Code values, expressions, data types, and operations. Learn to craft efficient let expressions, utilize custom functions, and harness powerful M Code functions like Table.AddColumn, Csv.Document, Excel.CurrentWorkbook, and Table.Group. Gain practical skills in working with various data types, manipulating dates and times, performing lookups, and creating custom functions. Discover advanced techniques for data aggregation, table merging, and approximate matching. By the end of this tutorial, become proficient in using M Code to transform and analyze data effectively, enhancing your capabilities in Power Query, Power BI, and Excel data analysis.
Overview
Syllabus
1. Introduction
2. Why M Code?
3. Files to download and follow along
4. Power Query Editor
5. 3 Places to edit M Code
6. Introduction to let expression
7. Define Expressions
8. Introduction to the 15 M Code Values
9. Data Types, Type value
10. Operations and Data Types
11. identify Expressions in a let expressions
12. Change Data Type
13. Group By and Table.Group function, first example. Why list within a list is so useful!
14. Identifiers in M Code and why you never use spaces
15. Hack Group By dialog box to make calculations not in dialog box
16. Keywords
17. Editing in Advanced Editor, including Shift + Enter
18. Syntax for let expression
19. All 15 M Code Values and Operators that are allowed for each M Code Value
20. Null value
21. Logical value and formulas
22. Text value and formulas
23. Number value and formulas
24. Why it is important to use value type and not data type for determining whether an operation is valid.
25. Relationship between Values and Data Types
26. Colaesce operator or if expression when you have null values?
27. Custom Column and Table.AddColumn function
28. Time value and formulas
29. Date value and formulas
30. Date.AddDays function
31. Duration value
32. Duration.Days function
33. Power Query Dates 1/1/0001 to 12/31/999 and how they Rule: many examples!!!
34. Calculate hours worked through midnight. This is basis for custom function later in video
35. Number.Round function vs. ROUNDDOWN vs. INT
36. let expression to define variables in formulas
37. Convert ISO Dates to serial number dates
38. Using Locale feature: Convert dates and numbers from one locale France to another United Sates
39. Duration.Days vs. Duration.TotalDays functions
40. Datetime value and Datetimezone value
41. Table, list, record values can hold more than one M Code value
42. List value and formulas
43. Aggregate functions require lists
44. List to expand rows from improper data set with a range of years in cells
45. Record value and formulas
46. Generalized Identifiers
47. Table value and formulas
48. Binary value
49. M Code lookup
50. Row Index Lookup examples
51. Key Match Lookup examples
52. Excel.CurrectWorkbook function
53. Primary Keys and lookup
54. Lookup columns for aggregate functions
55. Merge feature and Join Operations: Left Outer, Inner, and Left-Anti
56. Function value: custom functions
57. Hours worked custom function
58. On Premine folder and file paths and Data Connections dialog box
59. Fix and Append Text Files custom function
60. Append tables with Table.ExpandColumns function
61. Append tables with Table.Combine function
62. each and underscore explained!
63. Approximate Match custom function
64. Table.Group function fourth argument: GroupKind
65. Table.Group function fifth argument: Comparer as function
66. Summary
67. Conclusion
Taught by
ExcelIsFun
