DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)
HTML-код
- Опубликовано: 15 июл 2024
- DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)
Download files: excelisfun.net/files/DAMEwith..., pdf notes: excelisfun.net/files/05-DAMEM...
Alternative download links: Download files: people.highline.edu/mgirvin/A...
Pdf notes to read online: people.highline.edu/mgirvin/A...
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 RUclips 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
I have posted other free 2 hour M Code classes before, but this is the latest and best : )
43 year old and still following my teacher since 2010 maybe ❤
43 is a great year : )
Thanks for hanging out since 2010!!!
I've nearly 20 years on you and I've been following for a bit longer, I can remember when floppy discs were actually floppy. 😣😣
I am 65 and have no intention to let go. :)
@@johnborg5419 i don't think we should turn this into a competition.
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!
Thank you for your kind words : ) : )
Yes! Sunday-funday with M Code. Let's roll Team! Thanks MIke
Yes: Roll On Team!!!!!!!
ExcelIsFun by far the best Excel tutor!
I am glad to help, Pieter!!!
Thanks a lot, Mike!!!
You are welcome!!
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.
Your technique is perfect! For all new ideas that we try to get into our head, the variable is time. I am so glad that it helps. My intent of putting all the materials and story in the video is to get people to do exactly what you do: Take a week, try all practice problems, read notes and repeat where necessary. Great work, gvitulib!!!!
This epic video is amazingly good. Thank you very much for making this, Mike 💚
You are welcome, my ghostly friend : )
Thank you so much Mike.. there is so much to learn here...
(Hamy72)
My dear friend, Hamy72 : )
so much fun is to be had...
I just watched it all the way through, excellent Mike!
Thanks EXCELlent Teammate, Chris M!!!
Thank you very much Finished today but will be seen this Video many more times.PDF is very awesome
You are welcome! You are smart to take many days to study and have fun!!
You have an exceptional teaching method and M is the topic I enjoy the most in PQ. I appreciate your work, Mike.
Thanks for the appreciation and I am glad to help : )
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 Job!!!!!
Richard Hay, Word-Master-Poet, Thank you so very much for the insightful and kind and Xmas-simile description of my story telling. It only took 122 hours to make lol So I am glad that it gripped you for 2 hours : ) : )
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!...😅
OK, watched it in one sitting - no breaks.
This video is like almost PERFECT (I'd give it a solid 98% ;-)
• a little improvement: at 01:38:40 your ApproximateMatchLookup function didn't need the "mini let" for LookupColumn: it's used only once, so you can substitute that right in. This means you just define it as a straight single step function, no let at all. => even easier than what you showed. ;-)
• and in the category "Go Big Or Go Home": at the end you went ballistic and knocked it out of the park... into the next galaxy! A big exaggeration, but I mean it!! When it comes to GroupBy you are the GOAT, bar NONE!
People will have to see it, and then they'll believe it!
Great stuff, Mike, You're the GOAT!! 🙂🙂
@@GeertDelmulle You are amazing, Teammate Chief Editor Geert!!!!! : ) : ) : )
01:38:40 REALLY!?!?!? Whoa!!!!! I am not sure how that works. Does the function take and the input column and store it in memory? You must tell me : ) And, since the book goes to Mr Excel on Wed, I must change the book and show this...
GOAT = Generous Opulent Awesome Teammateshipness, right lol
OR
GOAT = Geert Offers Awesome Teammateshipness!!!!!!
@@GeertDelmulle P.S. I added your formula to the download Excel files and Power BI file and changed the pdf notes to show your let-less formula : )
@@excelisfun Mike, here's a few issues with this video:
• I don't see my feedback comment, but clearly you can/did because you responded to it.
• The primary download link doesn't work, I had to use the Alternative download site...
• ...over there I don't see the changes you mentioned above (yes, my little improvement to your ApproxMatch function works).
@@GeertDelmulle I just updated the download files so that they include your lovely shorter function. BUT... How does it work? Why don't we need let in the function, but we do need it if we create the formula in a column. How does it avoid the scope issue?
Using Comparer function is excellent in the last example
Yes, it is a real hidden gem : )
Excellent as always. Thank you Mike and looking forward to buying your new book.
New book gets through editing this coming Wed. Then in a few months it will be out.
Thanks Master Mike ;) Again a very good job. There is always a sweaty trick to discover in a DAME 2 hours vidéo. Wish Microsoft bless you ;)
MS blessing : ) : )
You are awesome
This is really epic one, i am also following since from 2,3 years especially when MS 365 comes into the Court
Glad it is epic for you!!! We are lucky that M 365 came into our court : )
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...
I heard your thoughts, RRR ; ) You are welcome a ton!!!!
M for MIKE!
M for MAGIC
M for MARVELOUS MASHUP!!! #goTEAM !!
Go Team!!!!!!!!!!!!
Thanks a lot Mike....!!!!....
You are welcome!!!
Can't wait to study it
misfun!!!!!
Thanks Mike. That was Great!!! :) :)
You are welcome, Formula Guy John!!!!
DAME...Double Amazing Mike's Excel ✌😉
: ) : ) : ) : ) : ) : )
@@excelisfun super fun is that "DAME 5" in Spanish means: "GIVE ME 5" 😂
@@Excelambda I am definitely giving you a high five!!!!!!
Thanks Mike, and thank you for the PDF notes as well. Printed already and will be permanently on my desk.
Don't tell anyone, but all of it comes from the book I just wrote...
@@excelisfun my lips are sealed.
@@lesterpotts6142 : ) : ) : ) : ) The book has a lot more, though...
Nice intro music. Thanks for this EXCELlent video amazing Mike.
You are welcome, Awesome Fellow Teacher : ) : )
Really great 😃
Glad it is great for you : )
Well done Mike, this is an epic video. Since it is White Monday I had plenty time watching it step by step. With the PDF notes this gives a fundamental theory. I think one of the reasons that M is still not in my head is that most of the time you can (with some extra steps) use the GUI. It is then tempting not to use M. But with your theory I will definitely read and modify the M code to try. Thanks.
You are welcome, Bart!!!!! misfun
You are so amazing, you are the best ✨
mcodeisfun ; )
It is amazing - I always learn so much new when I watch your videos. 😊
What did you learn this time?
@@excelisfun ?? instead of if to overcome null in a calculation, really great and easy to understand when checking code
1:39:43 My Mind is so broken: What if the lookup table is not sorted? - List.sort, what if the lookup table has text values instead of values? List. transform(number.from(_))and what would be better? List.Last or List.Max...
Questions, questions, questions ...
I know you wont give your students some supreme headache case of issues, but still to work with so properly prepared data sets and values is simple luxuries of Richie Rich
That is a great addition: sort and List.Transform(Number.From. In past videos I have definitely used the sort option. Thanks, Teammate!!!
I need your advice to learn M code from scratch to advance!. Thanks a lot for your great efforts.
I got you covered in this video!!!
REGEX comes to Excel!! EMT time!
What is REGEX?
@excelisfun Stands for Regular Expression. These new functions permit find and replace based upon patterns of words symbols and even numbers to extract text strings based upon character match. Most importantlybthey can be combined vwith other Excel Also contsion argumentd to make either case sensitive or insensitive sesrches/matches l. Extremely robust compared to other existing methods. Takes getting used to because itvuses a dozen or so symboles to rlrefine the extraction. But suppose you want all the phone numbers fro multiple pages bl of text or all the email addresses from a lengthy email which you paste into Excel. Extreme flexibility is its strength. I reference "come to Excel" because the symbols and the code structure they represent are lifted from other programming languages and
are well known to other programming languages.
The PDF notes are gold! In the video (38:59) you state that there's no Mod function in M. Actually there is. It's Number.Mod.
WHAT, WHAT!?!?!??!! I searched for the Mod function but could not find it. Thanks for the hot tip. I have not tried it, I hope it works that same as the one in the worksheet. Thanks for the hot tip, brianxyz!!!!
I just tried:
Number.Mod(Number.From([EndTime]-[StartTime]),1)*24
It doesn't seem to have the same algorithm as MOD in the worksheet.
@@excelisfun You're right. The underlying formulas are slightly different resulting in different results for negative numbers.
By the way, you did a video on this very topic back in 2020: EMT 1705.
ruclips.net/video/K4ImPRsi3vg/видео.htmlsi=_Q3BVewL8rFjPLr6
@@brianxyz I am so glad that I am on a Team with you. lol*100 This is not the first time that I have forgotten that I covered a topic. But here is the thing, I did do a diligent search for mod in Power Query M Code and came up with nothing... Very curious. Thanks, Longtime Teammate brianxyz : ) : ) : )
Thanks for the insightful video, Mike!
Also, I'd like to purchase your book when it releases, but I live in Indonesia, so shipping is a bit of an issue.
I wonder if there's any way that I can purchase a digital version of it?
Yes, at mrexcel.com, the publisher, Bill Jelen, offers digital versions : )
55:18, Mcode lookup? cant wait!
Some time ago I was doing an tool in PQ, and I wanted to play with Mcode aswell,
The result was nice, I had a column of records, and did you know that you actualy can filter a table based on record value without expanding the record values?
I would need to crate this from 0, since i left it in my old work place, but it was one of the Record. functions that would let you do it.
Now in what case would this be usefull? when you know mcode well and want save yourself a step, or when the data set is loaded to excel and the calculation for your work computer are heavy fot it, and any step-saving actions are actualy helping to speed the process up, and ofcourse lastly: Cos you can :)
Love this: Cos you can : ) : ) : )
@@excelisfun well, I like to experiment in PQ, sometimes out of nowhere you can create something surprising that suddenly has the option of being used in other projects :)
That how I learn :)
@@ExcelInstructor Me too : )
What's your view on the REGEX function in Excel? I recently saw a video about it on RUclips and it seemed complicated. I'm curious to know what you think about it, and if you could explain it a bit better. : )
I am clueless... I do not know what REGEX is. I don't have it... : (
Hello Sir, unable to download the pdf notes and file from the link given
I fixed the download link.
Using the PQM ‘Advanced Editor’ as your text ide for this language is an exercize in masochism and self-denial of the privilages of almost any other IDE on the planet. Its too bad they keep the powerful parts squirreled away in a modal popup window, for shame MS.
What is IDE? You are 100% correct: Microsoft is deeply idiotic for not giving us documentation for all there wonderful tools and not allowing us to access the features in dialog boxes and user interface...
Integrated Development Environment, what programmers often use to organize and work on software text files.
Adding the M lang extention into VS Code and unzipping the xlsx should not be necessary in order to view and work on more than one ‘M” code file at a time, imo. Excel needs more accessibly and sane UX access to this powerful tooling. Modal dialog for code edit is an insult to anyone with experience outside of Excel-land.
@@gentlemanbirdlake Well, it is an insult to Power BI land too.
@@excelisfun unfortunate but not surprising.
Not to seem too negative, I like the capabilities of M lang and made regular use of it when I use Excel, but I got very tired of having to jump between M files one at a time to work on code in that forsaken modal editor. Not to mention all the little tribal-knowledge work-arounds needed for example in order to fight the autocorrect changing what I typed to what it thinks I wanted to type and being wrong 90% of the time, like with renaming null from this video. We get de-sensitized to how bass-akward it is to have to learn these little tricks to get actual work accomplished. Its quite exhausting to use if you have any experience using better development tools.
@@excelisfun Here is a concise video with some examples of the sorts of programmer friendly features in a modern IDE that are not available in the Power Query advanced editor. ruclips.net/video/HBC7i1AbsyA/видео.html
Even the legacy Excel Visual Basic IDE you could view two code files side-by-side. Constantly jumping back and forth between two PQ M scripts to is just painful in the modal editor we have now.
I advocate that better developer experience would benefit everyone using Power Query M at this level.
I realize that they are treating M more as a domain-specific language than a general purpose language but the UX does not have to be so limited as it still is, imo. Its been over a decade basically unchanged which is disappointing.