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!!!!
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!!!!!!
@@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?
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 : ) : )
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...
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.
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?
What's your view on the REGEX function in Excel? I recently saw a video about it on RU-vid and it seemed complicated. I'm curious to know what you think about it, and if you could explain it a bit better. : )
I started watching your videos back in 2016, and I can't express how much I've learned from you over the years. Your teaching style is clear, engaging. You're the best teacher I've ever had, and I feel incredibly grateful for all the knowledge you've shared.
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
@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.
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.
@@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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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.
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 :)
@@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 :)
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!!!!
@@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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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 : ) : ) : )
