PQ #9: The Secrets of Power Query are in the M Code
Download files: https://excelisfun.net/files/DAMEwithMPT05.zipIn 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 Custom Functions
71. (01:42:48) Table.Group function & the 4th and 5th arguments
72. (01:50:55) Summary
73. (01:52:41) Mike “excelisfun” Girvin’s Power Query M Code book
74. (01:52:50) Closing
#mcode #powerquery #powerbi #powerbidesktop #lookup #expressions #values #freeexcelcourse
pdf notes: https://excelisfun.net/files/05-DAMEMPT.pdf Receive SMS online on sms24.me
TubeReader video aggregator is a website that collects and organizes online videos from the YouTube source. Video aggregation is done for different purposes, and TubeReader take different approaches to achieve their purpose.
Our try to collect videos of high quality or interest for visitors to view; the collection may be made by editors or may be based on community votes.
Another method is to base the collection on those videos most viewed, either at the aggregator site or at various popular video hosting sites.
TubeReader site exists to allow users to collect their own sets of videos, for personal use as well as for browsing and viewing by others; TubeReader can develop online communities around video sharing.
Our site allow users to create a personalized video playlist, for personal use as well as for browsing and viewing by others.
@YouTubeReaderBot allows you to subscribe to Youtube channels.
By using @YouTubeReaderBot Bot you agree with YouTube Terms of Service.
Use the @YouTubeReaderBot telegram bot to be the first to be notified when new videos are released on your favorite channels.
Look for new videos or channels and share them with your friends.
You can start using our bot from this video, subscribe now to PQ #9: The Secrets of Power Query are in the M Code
What is YouTube?
YouTube is a free video sharing website that makes it easy to watch online videos. You can even create and upload your own videos to share with others. Originally created in 2005, YouTube is now one of the most popular sites on the Web, with visitors watching around 6 billion hours of video every month.
