Power Query #9: The Secrets of Power Query are in the M Code
From excelisfun · · 1 hr 53 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.
If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.
Description
Download files: https://excelisfun.net/files/DAMEwithMPT05.zip 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! This video has subtitles or audio translations in these 126 languages. Click gear icon below video to set subtitles, audio and language. Topics: 1. (00:00:00) Introduction 2. (00:00:42) Files to download and follow along 3. (00:01:09) Why M Code? What is M Code? 4. (00:02:20) Power Query Editor in Excel, Power BI Desktop and Data Flows 5. (00:04:28) 3 Places to edit M Code 6. (00:04:44) Introduction to let expression 7. (00:07:17) Define Expressions 8. (00:08:00) Introduction to the 15 M Code Values 9. (00:10:32) Data Types, Type value 10. (00:11:06) Operations and Data Types 11. (00:12:38) Practice identifying Expressions in a let expressions 12. (00:14:26) Group By and Table.Group function 13. (00:16:13) Identifiers in M Code 14. (00:19:27) Keywords 15. (00:19:59) Editing in Advanced Editor, including Shift + Enter 16. (00:20:29) let expression cheat sheet 17. (00:21:43) All 15 M Code Values and Operators for each 18. (00:22:03) How to create a blank query 19. (00:22:44) Null values 20. (00:23:52) Logical values 21. (00:24:32) Text values 22. (00:25:26) Number values 23. (00:25:56) Why it is important to use value type and not data type for determining whether an operation is valid. 24. (00:26:54) Relationship between Values and Data Types 25. (00:28:40) syntax for if expression 26. (00:29:30) Coalesce operator 27. (00:30:31) Table.AddColumn function 28. (00:31:28) Time values 29. (00:32:49) Date values 30. (00:33:37) Date.AddDays function 31. (00:34:00) Subtract dates and you get a Duration value 32. (00:34:10) Duration.Days function 33. (00:34:33) Formulas and Date Values 34. (00:38:39) Calculate hours worked through midnight 35. (00:39:23) Number.From function 36. (00:40:57) Use the let expression to define variables in formulas 37. (00:43:23) Convert ISO Dates to serial number dates 38. (00:44:26) Using Locale feature 39. (00:46:23) Duration.Days vs. Duration.TotalDays functions 40. (00:47:03) Datetime and Datetimezone values 41. (00:48:05) List values 42. (00:51:26) Using a List to solve data transformation task: range of dates into rows 43. (00:54:09) Record values 44. (00:54:22) Generalized Identifiers 45. (00:55:11) Table values 46. (00:56:22) Binary value 47. (00:56:38) M Code lookup, syntax and execution for both Row Index and Key Match Lookup 48. (00:59:27) Row Index Lookup examples 49. (01:01:20) Key Match Lookup examples 50. (01:03:09) Excel.CurrentWorkbook function & Key Match Lookup 51. (01:04:32) Primary Keys and lookup 52. (01:05:00) Drill Down Feature 53. (01:06:38) Aggregate functions and Lists 54. (01:07:32) Merge feature and Join Operations 55. (01:12:30) Function value: Custom Functions 56. (01:13:44) Reusable Custom Function to Calculate Hours Worked 57. (01:18:47) Data Connections dialog box 58. (01:20:06) Reusable Custom Function to Fix and Append Text Files 59. (01:21:01) Csv.Document function 60. (01:24:51) Table.ExpandColumns function 61. (01:25:30) Table.Combine function 62. (01:26:30) each and underscore explained! 63. (01:32:18) Reusable Custom Function to Perform Approximate Match: Four Examples! 64. (01:34:36) Table.Buffer function 65. (01:35:41) Problem with back-to-back each key words 66. (01:36:06) let expression used in specific table scope 67. (01:38:14) Approximate Match Lookup Reusable Formula #1 (Two Arguments) 68. (01:38:44) Approximate Match Lookup Reusable Formula #2 (One Argument and a Hard Coded Table) 69. (01:39:17) Approximate Match Lookup Reusable Formula #3 (List.Accumulate function) 70. (01:41:42) Test three Reusable Approximate Match…