DAME 05: Master Power Query M Code & Become a Data Shaping Ninja (The Secret Behind Data Analysis)

[@excelisfun]

I have posted other free 2 hour M Code classes before, but this is the latest and best : )

[@Kingsley002]

Are we to expect any videos on Fabric ?

[@excelisfun]

@@Kingsley002 Not until late winter next year. I am in the middle for filming a basics class and a full finance and Excel class.

[@Kingsley002]

@@excelisfun Thanks for the reply. I can't wait for the M365 finance analysis.

[@excelisfun]

@@Kingsley002 Me either : )

[@Vivian-x2k]

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.

[@excelisfun]

You are welcome. Thanks for studying hard and becoming awesome : )

[@reng7777]

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!

[@excelisfun]

Thank you for your kind words : ) : )

[@PieterdeWit-gw3pw]

ExcelIsFun by far the best Excel tutor!

[@excelisfun]

I am glad to help, Pieter!!!

[@SalmanShaikh-zg1qn]

You are a master, the way you covers the topic was awareness

[@excelisfun]

Story telling matters, right? : )

[@unionafrican6094]

43 year old and still following my teacher since 2010 maybe ❤

[@excelisfun]

43 is a great year : ) Thanks for hanging out since 2010!!!

[@williamarthur4801]

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. 😣😣

[@johnborg5419]

I am 65 and have no intention to let go. :)

[@williamarthur4801]

@@johnborg5419 i don't think we should turn this into a competition.

[@chrism9037]

Yes! Sunday-funday with M Code. Let's roll Team! Thanks MIke

[@excelisfun]

Yes: Roll On Team!!!!!!!

[@mcwahaab]

You have an exceptional teaching method and M is the topic I enjoy the most in PQ. I appreciate your work, Mike.

[@excelisfun]

Thanks for the appreciation and I am glad to help : )

[@chrism9037]

I just watched it all the way through, excellent Mike!

[@excelisfun]

Thanks EXCELlent Teammate, Chris M!!!

[@richardhay645]

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!!!!!

[@excelisfun]

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 : ) : )

[@gvitullib]

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.

[@excelisfun]

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!!!!

[@kebincui]

Excellent as always. Thank you Mike and looking forward to buying your new book.

[@excelisfun]

New book gets through editing this coming Wed. Then in a few months it will be out.

[@GeertDelmulle]

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!...😅

[@GeertDelmulle]

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!! 🙂🙂

[@excelisfun]

​@@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]

@@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 : )

[@GeertDelmulle]

@@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).

[@excelisfun]

@@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?

[@rtrbs8383]

Thank you very much Finished today but will be seen this Video many more times.PDF is very awesome

[@excelisfun]

You are welcome! You are smart to take many days to study and have fun!!

[@azizurrehman6296]

This is really epic one, i am also following since from 2,3 years especially when MS 365 comes into the Court

[@excelisfun]

Glad it is epic for you!!! We are lucky that M 365 came into our court : )

[@kiwikiow]

This epic video is amazingly good. Thank you very much for making this, Mike 💚

[@excelisfun]

You are welcome, my ghostly friend : )

[@enochakabobby]

Thanks a lot, Mike!!!

[@excelisfun]

You are welcome!!

[@Excelambda]

DAME...Double Amazing Mike's Excel ✌😉

[@excelisfun]

: ) : ) : ) : ) : ) : )

[@Excelambda]

@@excelisfun super fun is that "DAME 5" in Spanish means: "GIVE ME 5" 😂

[@excelisfun]

@@Excelambda I am definitely giving you a high five!!!!!!

[@MrFredy65]

Thanks a lot Mike....!!!!....

[@excelisfun]

You are welcome!!!

[@KumarAnalytic]

In a folder I've so many Excel files and want to load into power query in one go as different data like Sales, Payroll, Attandance, Returns, Product etc. here I just want to load and I don't want to combine it. Thanks a lot ExcelisFun!!

[@excel_along_the_way]

Thanks Mike, and thank you for the PDF notes as well. Printed already and will be permanently on my desk.

[@excelisfun]

Don't tell anyone, but all of it comes from the book I just wrote...

[@excel_along_the_way]

@@excelisfun my lips are sealed.

[@excelisfun]

@@excel_along_the_way : ) : ) : ) : ) The book has a lot more, though...

[@Anthony_Lecoq]

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 ;)

[@excelisfun]

MS blessing : ) : )

[@sanjeevsoni6793]

Using Comparer function is excellent in the last example

[@excelisfun]

Yes, it is a real hidden gem : )

[@SyedMuzammilMahasanShahi]

Nice intro music. Thanks for this EXCELlent video amazing Mike.

[@excelisfun]

You are welcome, Awesome Fellow Teacher : ) : )

[@som8760-y7f]

Really great 😃

[@excelisfun]

Glad it is great for you : )

[@Hello-bn2yc]

Thank you so much Mike.. there is so much to learn here... (Hamy72)

[@excelisfun]

My dear friend, Hamy72 : ) so much fun is to be had...

[@rrrprogram8667]

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...

[@excelisfun]

I heard your thoughts, RRR ; ) You are welcome a ton!!!!

[@johnborg5419]

Thanks Mike. That was Great!!! :) :)

[@excelisfun]

You are welcome, Formula Guy John!!!!

[@timmybone13]

Thanks Mike! I also bought your new book, but I got my work to pay for it cuz we're all gonna use it at the office. (Small BI team of 5)

[@excelisfun]

Yes!!!! 5 for 1 : ) : ) Let me know how it is, Timmy!

[@timmybone13]

@@excelisfun A second question - do you do any online classes for working professionals?

[@excelisfun]

@@timmybone13 Yes, I have 3700 videos and over 100 playlists at my KZbin excelisfun channel for working professionals lol

[@timmybone13]

@@excelisfun Lol. Well yeah, I'm well aware as I've seen quite a few of them. I'm guessing nothing "live" though?

[@excelisfun]

@@timmybone13 O, yah... No live. Sorry about that.

[@lucaviglio1206]

Can't wait to study it

[@excelisfun]

misfun!!!!!

[@mecorowa5642]

It is amazing - I always learn so much new when I watch your videos. 😊

[@excelisfun]

What did you learn this time?

[@mecorowa5642]

@@excelisfun ?? instead of if to overcome null in a calculation, really great and easy to understand when checking code

[@ratulbanerjee]

You are awesome

[@barttitulaerexcelbart9400]

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.

[@excelisfun]

You are welcome, Bart!!!!! misfun

[@waleedalhasan85]

You are so amazing, you are the best ✨

[@excelisfun]

mcodeisfun ; )

[@ExcelInstructor]

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]

That is a great addition: sort and List.Transform(Number.From. In past videos I have definitely used the sort option. Thanks, Teammate!!!

[@williamantonio9743]

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?

[@excelisfun]

Yes, at mrexcel.com, the publisher, Bill Jelen, offers digital versions : )

[@MuhammadEldeeb]

I need your advice to learn M code from scratch to advance!. Thanks a lot for your great efforts.

[@excelisfun]

I got you covered in this video!!!

[@brianxyz]

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.

[@excelisfun]

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]

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.

[@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. kzbin.info/www/bejne/gWWsnoOIqM5mrMksi=_Q3BVewL8rFjPLr6

[@excelisfun]

@@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 : ) : ) : )

[@spilledgraphics]

M for MIKE! M for MAGIC M for MARVELOUS MASHUP!!! #goTEAM !!

[@excelisfun]

Go Team!!!!!!!!!!!!

[@richardhay645]

REGEX comes to Excel!! EMT time!

[@excelisfun]

What is REGEX?

[@richardhay645]

@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.

[@som8760-y7f]

What's your view on the REGEX function in Excel? I recently saw a video about it on KZbin and it seemed complicated. I'm curious to know what you think about it, and if you could explain it a bit better. : )

[@excelisfun]

I am clueless... I do not know what REGEX is. I don't have it... : (

[@ExcelInstructor]

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]

Love this: Cos you can : ) : ) : )

[@ExcelInstructor]

@@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 :)

[@excelisfun]

@@ExcelInstructor Me too : )

[@akalarun]

Hello Sir, unable to download the pdf notes and file from the link given

[@excelisfun]

I fixed the download link.

[@gentlemanbirdlake]

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.

[@excelisfun]

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...

[@gentlemanbirdlake]

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]

@@gentlemanbirdlake Well, it is an insult to Power BI land too.

[@gentlemanbirdlake]

@@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.

[@gentlemanbirdlake]

@@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. kzbin.info/www/bejne/fnOmaJxndsemr6M 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.