Free M Code Class from Basic to Advanced: Power Query Excel & Power BI, Custom Functions 365 MECS 12
Вставка
- Опубліковано 27 чер 2024
- Download Zipped Folder with All Files: excelisfun.net/files/12-M365E...
This video is a comprehensive lesson in Power Query M Code in Excel & Power BI.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: • Microsoft 365 Excel & ...
This video covers.
1. (00:00) Introduction
2. (00:26) Define M Code
3. (03:05) Power Query and M Code are in Both Excel and Power BI Desktop
4. (05:08) Advanced Editor button in Excel & Power BI Desktop
5. (05:39) Power BI Desktop “Enable Load”
6. (06:22) M Code Basics, tour of Power Query Editor and the three locations to view and change M Code. We use a Group By Query to learn about the basics of M Code.
7. (10:23) M Code Identifiers and spaces.
8. (14:21) let expression definition and structure.
9. (16:32) Expressions
10. (18:20) Insert or Delete Query (let expression) Steps: 2 possibilities.
11. (24:45) #shared to show Standard Library in Power Query
12. (26:32) Learn about 15 M Code Values: Null, Logical, Text, Number, Time, Date, DateTime, DateTimeZone, Duration, Table, Record, List, Binary, Function, Type
13. (33:15) IF function and AND Function in M Code
14. (36:23) Compare Excel Close & Load to Power BI Desktop Close & Apply
15. (38:45) Copy and Paste Tables into Power BI Desktop.
16. (39:35) Power Query Date and Duration Examples
17. (46:32) Table Values
18. (51:08) Record Values
19. (53:37) List Values
20. (59:05) Binary, Function and Type M Code Values
21. (59:40) Data Types
22. (59:40) Values Vs. Data Types
23. (01:01:38) M Code Lookup: Two-way Lookup, Column Lookup, Row (or Record) lookup. Also know as: Projection (lookup column) and Selection (lookup row)
24. (01:03:19) Diagrams for M Code Lookup
25. (01:06:04) Row Index Lookup
26. (01:07:15) Key Match Lookup
27. (01:08:55) Drill Down, Primary Keys and Lookup
28. (01:13:33) Custom Functions (5 examples to learn basics)
29. (01:17:50) Build reusable function.
30. (01:22:08) Build Custom Function as a Query Step.
31. (01:27:04) each and Underscore.
32. (01:29:47) Approximate Match Lookup M Code Custom Function (4 examples)
33. (01:39:13) Full Video Summary and Conclusions
34. (01:40:32) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/... . Artist: audionautix.com/
#Girvin
#excelisfun
#M365
#MECS
#Microsoft
#MCode
#PowerQuery
#PowerBI
#DataMashup
#LearnMCode
#FreeMCodeClass
#LearnMCodeFast
#MCodeMadeEasy
#MCodeLanguage
#CustomFunctions
#MCodeCustomFunctions
#PowerQueryLookup
#MCodeLookup
#MCodeSpecifications
#datascience
#PowerQueryMCode
#DataAnalysis
#MicrosoftDataAnalytics
#PowerQueryMCode
#PowerBIMCode
#McodeAndDAX
#datacleaning
#datatransformation
#ETL
#advancedexcel
#mcode
#powerbi
#excel
#businessintelligence
#businessanalystcourse
#mcodetutorial
#mlanguage
#mlanguagetutorial
#powerbitutorial
#powerquerytutorial
Dataflows (Power Query Online) is introduced in the next video MECS #13 : )
There are a practice files for both Excel & Power BI Desktop! The practice files (homework) are in both files, but instructions are only in Excel file on the HW(1) worksheet.
Hi Mike great Video.
19:50 Probobly under 1 of my comments I already showed a solution for dynamic type pickup. so you can use exact same technique to rename columns. and when used with M code if statement you can create quite advanced way of renaming columns, including an excel Table which would have table column names and its renamed column.
@@ExcelInstructor That is in the next video : )
41:53: When im trying to explain adding values in PQ as easy to understand example im using fraction addition. becouse when adding fractions denominator has to be the same for all the fractions that we want to add, so in a sense Power query "denominator" is the data type we are opereting on.
@@ExcelInstructor That is a great example : ) : )
1:02:05 Q1: That's easy :) [Supplier] is the column name we are extracting values from, and {2} is 0-based position from the top of the list from the [Supplier] Column extracting actually position number 3. PQs 0,1,2 = to 1,2,3 :)
1:02:09: Q2: This is bit more complex but still very easy :) so let me break it down to parts:
Part 1: the function itself: Excel.Currentworkbook() a function that return every readable object for PQ into a Table of values (ranges, named ranges, Printing ranges, Tables, objects from name manager and even printing areas)
Part 2: the curly bracket: within it we have field access operator pointing to very specific record named "LookupExample" basically this creates a record that contains an object and a name.
Part 3: [Content] that is pointing to specific column that is actually always the same for every imported document. this Column contains table object.
so all this "mess" looks to every object in excel workbook, then searches for a specific record containing a Table, then the table is "extracted" from the record and displayed into basic table we can start working on.
Mike you are teaching the whole world directly by your videos, indirectly by teaching instructors like me who are carrying this legacy ahead.
You are truly amazing and best in the world . Inspiring !!!
Yes!!! Love to hear this. It has been my goal for 15 years here at UA-cam to provide free education for the world in hopes that it would spread and shower Excel efficacy and fun onto the world.
Pavan Sir,
Like Mike, You are also helping countless People through Your UA-cam Channel.
We are Grateful to Mike and You 🙏🏿
@@ankursharma6157
Ankur it's absolutely my pleasure doing this and seeing the change
@@ankursharma6157 @PavanLalwani : ) : ) : ) : ) Go Team!!!!!!!
@@PavanLalwani I did not know about your awesome channel!!! I just subscribed : ) Go Team!!!
In my opinion, the best Code M step by step course I have watched so far. This 365 MECS series are like top ranked Oscar´s awards movies. Thanks a lot Mike !!
You are welcome a lot!!!! Thanks for the Oscars, gvitullib!!!!
Dataflows (Power Query Online) is introduced in the next video MECS #13 : )
Top class video by a legendary man Mike Girvin.....thanks for sharing...
Yes, RRR-Long-Time Viewer!!!!! You can watch and help, RRR: ua-cam.com/video/3ZkIwKBVkVE/v-deo.html
Mike deserves all the praises in this comment. He is just in a different level!
Glad you like this!!!!
This is the most comprehensive Power Query/M Code video I've come across in UA-cam so far, and I've already viewed weeks of Power Query/M Code videos already. I wish I came across this video a lot earlier and I could have saved a lot of wasted time.
I wish you had too. But Google seraph is not determined by quality and value. Glad you found it!!!
I'm speechless to see your hardworking, dedication, enthusiasm, and unconditional support to help us.
Thanks a lot!
Yes!!!! That has been my goal for 15 yeasr here at UA-cam : ) : ) Tell all your friends about the free education here at excelisfun!
@@excelisfun off course, I'll, let me tell you whenever somebody asks me the best UA-cam channel to learn Excel from. I always recommend this channel. I myself leanred from here.
@@qamaruddinkhichi904 : ) : ) : )
I just dropped out from one of the top universities here in Korea and now I am focusing all my time and efforts in learning every detail of your lectures and your book. The difference in the quality is so outstanding that I feel ashamed even trying to compare. Thank you so much for your knowledge and effort, and more importantly your passion. I want to go back in time and slap myself who thought Excel was nothing more than a boring technology. I am trying to move away from all the fancy software engineering, machine learning, ai trend that I blindly pursue and am planning to focus the next several years on understanding and mastering Excel. Thank you so much and I hope you have a great day
I am so happy to help!!!! It is 100% true for many decades: Colleges and Universities around the world have abjectly failed to proper train graduating students. Almost everyone must know Excel (especially in the data-driven decision era), but almost no graduating students are taught Excel. Maybe they take one high-ill-informed class in the matriculation process, but they ultimately leave the university without a key skill. This is why I became a professor over 2 decades ago. The funny thing is 2 decades have past since I became a professor, but there are still very few teachers in the world who are actually teaching students what they need to know, in this regard.
I am super glad to help you!!!!!
As you watch and study my videos, remember these are not free: I do change a thumbs Up for each video ; )
good luck!
Thanks for sharing , I am amazed how people like you are sharing such useful and precious knowledge for free.
Thanks once again for sharing.
I am happy to share free education for 15 years here at UA-cam, to help people like you and the rest of the Team : ) You can help get this video to more people, just be sure to watch end of video: ua-cam.com/video/3ZkIwKBVkVE/v-deo.html
This is 'next in line', excellence work of Mr Girvin. Tireless keeps giving us knowledge. Unbelievable. Thank You!
You are welcome for the believable-unbelievable, Bucs 2003!!!!!
The best Video on M-code on the Internet for sure. The PDF notes are superb. Couldn't have learned excel without Mike Girvin :)
Thanks Nader : ) : ) : )
I’m glad I already watched your video on LAMBDA worksheet function. It really helped to link the related concepts from that video to this one. This is high quality learning material! 👏
Mike honestly teaches for common person. I’ve paid for a lot of training and have learnt a lot but Mike just makes it so simple for all. If I ever visit the US,I’m taking this dude out for lunch!
Glad it all helps! Can't wait for lunch ; )
@@excelisfun definitely. If you’re ever in NZ visiting Chandoo, I’m local to where he lives. Lunch offer stands here as well. 😊
@@joedi We could have a regular Excel Paaaaaaaaarrrrteeee!!!
The good thing about epic videos are that you can go back over and over again, plus with the addition of time stamps great thank you very much.
That is exactly why I make them: time-stamped one-stop shopping!!!! The problem is, the vast majority of Excellers do not like long videos. They want a quick answer. And the UA-cam/Google algorithm hates my videos and does not promote them well... But for smart people like you: Bam!!! I got you covered ; )
oh my gosh, that was a lot of content jammed packed into 1h 40 mins. I sat through it and followed along for the whole morning and afternoon.
Great for beginners, the pace at the start was great, lost you there a bit during the Custom Functions part where there was 5 methods 1:24:42 , went to rewatch and rewatch at a slower speed (I usually use 2x so went to 1.5x or 1.25x)
And then, just as I thought I was back on track, during the last 5 mins of the double "each" part and the 3 different ways to write a match lookup function in the advanced editor, it lost me, 1:36:16 , due to the tabs and spacing, but I went to your "12-M365ExcelClassFinished" file and slowly untabbed and read through each line and rewatch your video and it helped.
Really great teacher! Will just need some time to process everything after absorbing all that content at one go and I will definitely go back to this video for reference along with your word document whenever I need help to refresh concepts again.
Brilliant M code tutorial!
Your study methods are perfect for people who REALLY want to learn. I put so much all in one class: long videos, files to practice, finished solutions, pdf notes. But only those that take 10 to 30 hours of concentrated study will come away with good skill. Awesome stragey, Snowy!!!!
@@excelisfun yes, I appreciate all the comprehensive files and notes! Thank you!
I am as excited about this series as I was when my wife gave me the Marvel Infiniti boxed series. You are a greater hero than any of them. Thank you!!
You are "Infiniti boxed" welcome for the series!!!!!!
Thanks for the interesting intro to M Code, it is certainly a powerful way to use the Power BI application to clean and prepare data ahead of analysis. Love the way you give clear and concise explanations with multiple examples and your boundless enthusiasm for the subject matter. I like that power query makes you think more about data types and how the data is structured versus how to achieve the required structure and have noticed that with the newer functions in Excel that Microsoft seem to be pushing for a more highly structured approach to data on the grid which can only be a good thing.
Only a good thing! For sure : ) Glad you like the video, Roy! In the next video we will see how for even mundane topics like connecting to a data source and importing data, if we know some M Code we can do some magic things!
No other video is available like this in respect to M Language.. Thanks for making this descriptive video..
You are welcome for the description!!!
Absolute exceptional
Waooo...
Completed to simple file and step by step
thanks very much...
thanks ....
I have a new version of this video and its even better. It comes out tomorrow.
Awesome Mike! I definitely need to get better with M code to take my PQ abilities to the next level. Thanks for another great video!
This video should be good for an M Code launch into Power Query awesomeness!!!!
Literally just started using power query this week and have a development goal for the year of learning power BI. Thank you for uploading!
You are welcome, anthony!!!!!!
It's hard for me to even express how grateful I am for such a great piece of work... Thank you so much !!!
You are welcome so much! Tell all your friends and colleagues : )
This video is great. i am using power query for about 5 years but this video taught me all new aspects of it. thank you mike
I love to hear that!!!! With M Code you will have so much more power. Next video has lots of M Code tricks too : )
Sir you are amazing teacher
Thank you : )
Duuuuuude! I've ben begging for a video like this!!! Thanks a ton!!!
You are welcome a ton!!!!!
A genius video. All in one on M Code. I wish I could like it more than once. Thank you.
Thank you for the many Thumbs Ups : ) : )
You are always my best !
Glad to help!!!
This Channel is a goldmine! Thank you!
You are welcome !!!
This is so excellent and insightful, Mike. You are really making a difference and we appreciate
Glad you like this MECS class!!!!
You made my day Mike. Thank you very much.
Yes!!!!! Makinbg Days is THE best : ) : ) Tell all your friends who want to learn M Code : )
Amazing work! Did a fantastic job in preparation and presentation and the content is really helpful!
Appreciate what you have taugth me
in this video and the others! Mange tak.
Thank you VERY much for your kind donation!!!! It really helps me to keep going : ) : ) : )
I would like to say to you that you provided almost all of the required basics for any one to start working based on power query editor and with little help of Copilot and chatGPT4 any thing could be done based on this knowledge provided in your video , in the End , that was a great video
👌👌👌
Glad it helps! Contrary to many human's beliefs, if you don't understand M Code (or any given topic) well, Copilot and chatGPT will just get you in trouble.
Awesome Explanation and exploiting many uses of implementation. Very thorough and exemplary!!
Glad you like it!!!! Tell all your friends about the high caliber and that it is free: they won't believe it lol
Thanks so much Teacher!
Thanks for sharing - this is so well done. I've been using Power Query since 2014, and I still learned a ton! ... M = data Mashup, who knew! :-)
Great video Mike!
Glad it was helpful!
Stunsing as always ❤❤
Glad this M Code fun helps!!!!!
Thanks for sharing. This is the best class I 've ever seen. I like the way you teach a real Master.
You are very welcome!!!!
Thank you Mike such a broad topic handled so thoroughly and well.
You are welcome, John!!!!
I like the way you teach M Code step by step. Video and all files you created are awesome. 👍👍 Thank you very much Mike 🙏💚
You are welcome very much! pdf files, Excle files and videos are ghostly fun ; )
Coucou 🧡💜❤️❤️💙💙❤️🧡💜❤️❤️💙💙❤️🧡💜❤️❤️💙💙❤️🧡
@@christellelemaire9749 : ) : ) : ) : )
Added to playlist! Thank you for your efforts. ❤️❤️❤️
You are welcome for the efforts!!!!!
Great input. Thank you for your support.
You are welcome for the support!!
I am definitely not a Power Query expert. In the relatively short time I’ve been using it, I’ve noticed a couple things related to hardcoded values:
1. Avoid if possible by doing things like making a variable for the columns names
2. If they can’t be avoided, try to put those steps at the end of queries to reduce the number of steps that reference it in someway
What a fantastic tutorial. I’ll have to get these notes
Great advice!!! Yes, check out the notes, they should be good!
This is the one I’ve been waiting for!!
Yes!!!!! All throughout the month long production, this video has been waiting for you too, Martin ; ) P.S. tell all your friends too : )
Appreciated your huge effort Mike!
You are welcome for the hugeness ; ) Tell all your friends about the fun!!!
Boom!Real Fun Class,Picked Up Some Amazing Tips...Thank You Mike :)
Glad you got some tips, Bike Brother!!!! What tips did you learn?
Thanks for your amazing Excel content. Very helpful to become an Excel Wiz.
Yes indeed: M Code makes great Excel & Power BI Wizzes!!!!
Thanks man. I needed this
Awesome Mike with another EXCELlent video. Thanks for the share.
You are welcome for the share, Fellow Teacher!!!!
Thank you, i fall in love with Excel because of you
You are welcome! Glad you love it : ) : )
When you use EACH, the _ represents the whole record or other object returned from each row and in many contexts, using _ is not required, but you can put it in just before the [ ] and it works.
Thanks for that tip : )
Commenting for the algorithm.
: ) : ) : ) : )
Excellent work and easy to follow!
Yes!!! Glad it helps : )
An excellent tutorial (again :) ) for the PQ/M, even for experienced users! 💯Thank you very much!🙏
You are welcome very much!!! As an experienced PQ/M pro, what things in the video were helpful or what did you learn?
@@excelisfun ☺😇 I'm not a pro 😁, but I've already implemented a few semi-automatic solutions with PQ in a corp. environment. In the last 2-3 years, I've completed a few PQ courses, some of them were comprehensive, but somehow your approach, teaching method and your way of thinking is much closer to me, not only in that topic, but also in the others. For example, the LOOKUP methods were not explained so well and understandably in any course. In fact, there was no mention of it anywhere in this context: analogously to excel (INDEX fx).
This tutorial made the whole picture and syntaxes much more understandable and clear, which so far I haven't used 100% consciously.
(I mean here copied sources.) So thanks again :)
I'm already excitedly waiting for the next part MECS13, I'm planning that for the weekend :D :D :D
@@tibibara Yes!!!! The next few MECS are all about Power Query and M Code. You should enjoy. I am glad that the stories that I tell to try and make complicated things less complicated work for you : )
Thanks for sharing the same, waiting for such video for a long time 🙂
You are welcome for the share!!!
Thank you very much Mike.
You are welcome very much, JMPRMAIL!!!!! Tell al your friends too: ua-cam.com/video/3ZkIwKBVkVE/v-deo.html
Thank you for your service and tips sir you are the best
I am so glad to help!!!!
thank you so much. Learnt a lot
You are welcome, SR!!!! P.S. In the sports of BMX, Skateboarding and Boomeranging, SR means Stay Rad : )
Hi Mike, late to this party but thank you for this tutorial. Much appreciated.
Paaaaarrrrrrdeeeeee!!!! Welcome to the party, Lester!!!
I tweeted a link to this video Mike. It's the least I can do, I mean the first PQ video I ever watched was by you 5 years ago and I've never looked back 🤘
Yes!!!!! 5 years of Power Query fun!!!!!
This is in absolute the best video and the best explanation I have seen and heard online. It's a PowerTutorial that Data Meshed all my confused knoledge of M code and cleaned it and put it in Steps in my mind. Thank you so much
You are welcome. I love how you said this: "Data Mashed all my confused knowledge of M code and cleaned it up and put it in steps in my mind"!!!! : ) : ) : ) : ) That is EXACTLY what I have tried to do with the MECS class and these very long one-stop shopping videos: for a given topic: put the important elements of a given topic all in one place, and present it in an order that helps to make the complicated, less complicated : )
@@excelisfun ☺
@@alterchannel2501 : ) : )
Huge like for the video! Keep up the good work!
Huge thanks : ) : ) : )
Thank you for sharing it!
You are welcome!
Great video. Definitely took a fair amount of elapsed time and several settings to get through!! There has been a shortage of available videos on
PQ so this one is a valuable addition. Maybe the subject of your next book?!?!?
Glad you like it, Richard!!!! Yes, not many videos on UA-cam about M Code. I am stumped from a marketing perspective, cuz I can't seem to get a free video like this out to people, and the google algorithm hates most videos that I post. Too bad good free material doesn't get to more people. I would love to write a book about PQ and M Code. I am planning to, but I always am so slow at things these years... lol
Thank you for this session
You are welcome for the M Code session!!!
Thank you so much Bro ,Great appreciation will follow each steps for M code
You are welcome, onsaeli!!!!
Fantastic video. Thank u Mike 👍
You are welcome, Martin!!!!
Fantastic
Thanks!
Thank you very much for the donation!!!! M Code is fun : )
This is Amazing. Thank you so much
You are welcome so much!!!!
Thanks mike, great big video, always something new to learn ;)
You are welcome, anthony!!!
This is just amazing
Glad you like it!!!!
Great video, thanks
You are welcome!
Yes!!! You are awesome
Thank you!!! I hope you enjoy the M Code fun and will tell all your friends : )
useful video, thanks for sharing
You are welcome for the useful share : ) : ) : )
thank you for your share. You help me solve my problem.
You are welcome!
Thank you very Much .....Sir...
You are welcome very much!!!!
Thanks for sharing Mike.
You are welcome!!! Tell all your friends : )
For sure
Thanks, sir, for your efforts
You are welcome!!!!!
May God bless you Irvin
I hope you will like the video : )
@@excelisfun I like it. There isn't a video that you have made that I have watched and has no practical application in my life. You are the gift that keeps giving. Thank you Sir.
@@morenabalatseng8003 You are welcome for the gifts!!!! Just keep watching, learning and clicking that thumbs ups : ) Tell all your friends too!!
M Code by Master Mike ❤️ I always used to get confuse in list within list ..but now I understood it thoroughly . Thanks a lot ❤️
Yes, the little details of M Code will make you a master. As soon as we se the ingenious beauty of how a list within a list is used in the Group By feature to get the 1) name of the new columns, 2) aggregate calculation, and 3) data type: BAM!!!! It makes prefect sense. This is a good example of why it takes so long to make videos like this (this one took 162 hours). I have to search and hunt for the perfect examples that illuminate the meaning of a certain topic, then I have to string together the related topics in the right order so it is smooth and easy to understand. Further, in the section of the video about lists, after I completed the filming and editing for the list section, I had to come back two times to insert new examples to help flush out the beauty and power of lists.
You are welcome a lot, shubham!!!
@@excelisfun Hats off to You ❤️..162 hours OMG ...You are really a true Excel Master. I luv the way , you are teaching anybody who has little knowledge or no prior knowledge can easily understand ❤️❤️
@@shubhampawar8506 That is one of my specialties as a human. Since I am not that smart, and graduated High School illiterate, and had to learn as a dumb adult how to do things. I get it. I can build stories to make complicated things less complicated. My stories make few assumptions. My storied try to anticipate all questions and place the answers into the story in the right order. This is why the videos and stories take so long to make. I plan a story that even dumb people like me can understand. Although PHDs and Engineers and Mathematicians can all watch my videos and learn stuff (they are the ones who could understand it if I made lots of assumptions and let out details), I try to make my video stories so that we all can understand, learn, gain power and have more fun!!!!
@@excelisfunYou are Intelligent and Smartest person for me ❤️❤️Lots of respect for You ❤️
@@shubhampawar8506 : ) : ) Hard work and determination can get us anywhere : )
Amazing Mike ... this is what i was waiting for ...Thanks alot
Yes!!!! The wait is over. Watch and be happy, Hussein!!!!
@@excelisfun Yes Sir
@@HusseinKorish : )
thanks for sharing 👍
I wish this came out much earlier, but still watch the whole thing to fill in my knowledge gap. Thank you !
I published a video almost exactly in my MSPTDA class like this back in 2018: ua-cam.com/video/NS1AvfV9BeI/v-deo.html
Where where you, unigirl lol BUT: The good news is: better late than never. I hope all the M is great for you! P.S. when did you discover my excelisfun channel? What topics do you want? What field do you work in?
@@excelisfun oh wow back in 2018 ? How could I miss that one ? I watched your channel a while back and somehow it fell off my radar lol but good to be back now. I work in aviation so a lot of non-accounting stats that I do besides the typical financial. Is there a way that power BI / Power Query can convert different PDFs to a table ? I get weekly pricing notifications from different vendors sending in PDF so if I somehow can drop the PDFs to a folder, refresh the table and get the weekly summary, that would be amazing. Thank you !
@@unigirl123 Yes - the next video in this class is all about importing data with many examples. There is one pdf example. BUT: there is one HUGE problem with your situation: by definition, pdf files are not designed to carry data from one system to another, like csv, txt, Excel, xml, json files. So there is NO way to guarantee that results are correct with file types like pdf and picture. Not only that, but the from Folder Feature (which would be perfect for this) can't interpret pdf data in a reliable way... Your situation is an example that plagues the planet earth: most people are ignorant about the structure of data. In a data driven decision world, this is a HUGE problem. The solution is to have the tables sent as csv or txt or some other file format where an easy solution can be created.
Super Dope 😅❤
Just wow🎉
: ) : ) : ) : )
Excellent, short videos cover the lot of M Code Topics
Glad it is EXCELlent for you. But I do not understand what your comment means? "short videos cover the lot of M Code Topics "
The useful content and precious video thank you very much for sharing
You are welcome for the share and useful content!!!!
@@excelisfun
My English seems not good enough 😁😁😁
Thanks again sir
@@adelmerah : ) : )
Ďakujeme.
Thank you so much for your kind donation!!!! : ) : )
Mike :) at 39:52 I am French and my regional settings are set to France as well, when I chande the type if the first column to Date (using US locale) I get the format DD/MM/YYYY. But in your case I would maybe suggest to use the Date.ToText with the format and culture options to display the date the format you want
I think you can use Data Type Using Local and get the dates to work.
I wish you'd done this a few years ago , a great resource for anyone starting and a good refresher,
I always forget records don't need " " ,
really looking forward to functions, usually muddle through but never know when to start with a variable, or with let and then the variable or if it needs to b for table or column. Now my head hurts.
Thank you once again.
I did make a video almost identical over 4 years back: MSPTDA #9 : ) I thought you already watched the MSPTDA class. Anyway, this MECS video #12 has all the content from MSPTDA #9 but it is presented in a smoother and more clear story : )
I got you covered in this video if you want to learn Custom Functions, william! P.S. sorry about the head hurting : )
@@excelisfun Seen it, but thanks, the msptda series was incredibly thorough, really enjoyed it.
@@excelisfun Yes loved the msptda series, my problem with custom functions is when writing from scratch, not converting, and when to to be used by using 'invoke custom function' to act on a column, or when to have a table function select the function and then select the table to act upon, I've done both but never quite sure which I need without experimenting.
@@williamarthur4801 If I understand you correctly, I just create reusable functions (invoke custom function button) when I have to use the formula more than one time. Otherwise, I just write it in the column.
@@excelisfun Sorry for not getting back soon, this is the sort of thing I meant,
this is for a running total after having grouped the date, so when converting delete all before index;
(RunningTotal as table)=>
let
#"Added Index" = Table.AddIndexColumn(RunningTotal, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum ( List.FirstN(RunningTotal [Units], [Index])))
in
#"Added Custom"
this works on the column, but this one renames headers with what's (Goodly Challenge) in first two rows, and (i think),
needs the 'as table' ; then select the function and the tabel to work on in drop down;
oh, he used Table.Record but I like this.
(NewN as table ) => let
NewName = List.Zip( { Table.ColumnNames(NewN),
List.Transform( Table.ToColumns( Table.FirstN( NewN ,2) ),(_)=>Text.Combine(_," ")) } ),
Custom2 = Table.RenameColumns( Table.RemoveFirstN( NewN , 2), NewName )
in
Custom2
I shall stop now, and thank you for taking the tiem to reply.
Thanks
You are welcome!!!!!
Respect ...👍👍
Thanks : ) Do you use Power Query often?
@@excelisfun yes power query is a essential part in my work but before your video I have no idea about m code so thanks for your video...
@@TheAlwaysGoel You are welcome! Power Query and M Code are power in the working world : ) Be sure to tell all your friends !!!
Finally the first to comment
😁😁😁😁
Yes!!!! First Place Trophy for you, Temidayo!!!!
Congratz :)
Thanks a lot for this video
Sir can you please give a link of video ,as I had watched earlier but don't remember now and need to refer the same, where you have explained how to fetch discount amount where there are three columns, 1. from no. Of units,2. To No. Of units and 3. Disc rate
Mike, or anyone else - I have been looking for a way to use Power Query to reconcile two datasets which ONLY have values in common. Both datasets include dates, but the date of a particular transaction in one dataset may be a day or two earlier or later than the other dataset. For example, there may be a dozen $50 transactions on one date in dataset A, but they may be scattered over two or three days in dataset B. Is there a way for Power Query to perform a few different merges (left outer, right outer, left anti, right anti) using a "slightly fuzzy" date comparison with a "strict" amount comparison, so that I can use the merges as support for the reconciliation? Every tutorial I have seen used datasets with very cut and dried index columns that make this type of reconciliation a relative cinch.
I would appreciate any help offered. These datasets can have over ten thousand transactions per month, making manual reconciliation very tedious and time consuming.
Mr Girvin
which is better to automate tasks in excel, python tools like ( xlwings and pyxll ) or VBA
which one I should go with
I am not skilled in those areas. I cannot answer your question. Sorry.
Hello I hope you can help me, I need to create a column with the information o a all base text column. I need to find the “purchase order” it has 8characters the first always start with “2”. The text is very long so, some time there is a bunch of characters “2” but do not complete the full criteria (8 characters from 0-9). Some tienes you will find a long string of numbers that start with 2 but is longer than 8 characters, so the 9th character after the 2 must be out of the previous list 0-9. I have been doing a lot of tries however it always says that i have a cycle reference. Btw if the formula do not find any purchase order it will return “no PO”. I have trying for 3 days without luck at this point I do not know if this could be posible or I have to do it with DAX, share some light thx