Two Notes: 1) Homework practice problems to the end of the workbook file: "04-M365ExcelClass.xlsx". 2) There is a Video Progress Checklist that can help to track progress through this long video. The "ProgressChecklist" worksheet is in the file "04-M365ExcelClass.xlsx" and is in a separate stand alone file named: "MECS04ProgressCheckList.xlsx". (Checklist idea came from Dom Tomoff at LinkedIn).
Hi Mike, I was wondering about excel and memory, Probobly soon I will start teaching excel in my work, so im wondering - is there any formula / addon / extension that would show much much memory specific cell input takes? Just to visualy show that 1 takes less memory then "1" etc, Do u know a way of measuring this?
@@SyedShadabHussaini1996 What I can say is I work for large auto manufacturers and occasionally make Excel dashboards. If I ever get stuck. I come here. Any of his base courses are gold. I'm very excited to watch this new one. That's all I can say. Best of luck.
@@SyedShadabHussaini1996 You can watch my Introductory video that teaches how to find videos and classes that can help you at my UA-cam channel: ua-cam.com/video/l1-1aVgFth4/v-deo.html
I'm too broke to pay for online courses. Watched a lot of other videos on UA-cam, but they just beat around the bush. Then I see this video. 4 hours straight to the point. No courses mentioned. No bs. Sir, you dropped this 👑
@@excelisfun That's awesome. Giving away so much knowledge for no cost where others are charging for the same knowledge is appreciated. Thank you so much.😊
There is a saying “Nothing comes free”. But you are proving it’s wrong. Your commitment in providing free Excel education to all is a matter of delight to interested students.
My goal here at excelisfun at UA-cam for past 14 years is to provide free Excel Education to the World!!!! I try to prove the statement “Nothing comes free” wrong, but I actually do charge students a thumbs up for watching and learning ; ) lol
Boom 💥 💥 💥 Here we have it the mammoth episode, for which we were waiting for more than couple of week. And today only your book was delivered on my door. Small way of saying thank-you for your work. Learning never ends with you.
@@excelisfun To be honest: 1. I don't know all the stuff in the video. 2. I haven't had the time to watch it in its entirety. It is very long and complex ("an epic video") I'm writing now my first Excel book: "120+ amazing tricks with the SEQUENCE function". Some examples can be seen here, in a webinar I gave about a year ago on this extraordinary function. You are kindly invited to watch in the following link: ua-cam.com/video/gx9IGR2zsO4/v-deo.html Enjoy 😎
Sir if i call you an excel wizard , it would be an understatement. The way you explain everything in depth , big fan ❤️. You are significantly contributing to this world.
I released video yesterday, Sunday. Today, Monday, I added some homework practice problems to the end of the workbook file: "04-M365ExcelClass.xlsx". But you will have to re-download zipped folder if you want access to them.
Thank you very much for your kind donation, Ajay : ) : ) : ) : ) The donation helps me to keep making videos for our awesome Online Excel Team.. Go Team!!!!!
Respected sir...you are raising the standard of education in your videos day by day... when we use these tricks in office ,people think us whizard also.... your teaching skill is better then the masters from whom even you learn..🙏🙏
Thank you very much, sujeet!!!! It is true, I am not the smartest about tools and methods (not one of those masters), but I do try to master the art of creating good stories that can make complicated things less complicated : ) : )
I've never commented on a UA-cam video before. But I wanted to let you know how much I appreciate your videos. I also purchased your book and it's amazing. Thank you, Mike.
Thank you for making your first UA-cam Comment ever, Fernando!!!!! We actually have a lot of fun in these comments below my videos and share ideas and learn a lot. Welcome to the excelisfun comments!!! You are welcome for the vids and thanks for buying the book : )
Mike, when beautiful things come out of people, I often times wonder what was placed inside of them. I would enjoy hearing your story someday. Thank you for being here on this Earth.
Thank you for your kind words, Daniel!!! I am happy to be here on earth to try and make it a better place, fulfil my duties, have fun and get rad : ) That is how I think of being on earth. My story can be condensed down to: I follow my heart and do what I love; since all else follows from love, the hard work, dedication and perseverance is automatic; then I can have fun with construction stories about Excel and Data Analysis to try and make complicated things less complicated; and occasionally get rad with BMX, skateboarding and boomerangs.
I should call it the HOLY BIBLE OF EXCEL... I have gone through umpteen videos but never ever came across such an effortful, useful, and easy to understand tutorial on the subject. I thank you from the bottom of my heart for bringing this video... God bless you 🙏
You are welcome from the bottom of my heart!!! This is what I have been doing for 15 years at UA-cam: Provide full free education that is telling stories to make complicated things less complicated : )
Thank you for your kind donation, Excel (A-Z)!!!! What are some of the useful things that you took away from the video? I see you are an Excel UA-cam Teammate : ) I just subbed to your channel!!!
Never had to comment on UA-cam. Mr. Girvin has to gift to teach, to connect with students along with the ability to explain very clearly. He purchased the latest book just because the author is, Mr. Mike Girvin. Thank you for being able to help. You are an outstanding teacher, and you got me hook on UA-cam, but now I will focus on that great book that you put out there for us. Respect
That is soooooo true, Chris M. Too much to take in for one video for most people. BUT, in your case, you know and have seen most of the examples that I show, and since this is a different story and a much better story about Data Analysis tools than I have told before, you could actually watch it like a 3 hour 44 minute movie on Saturday night - just be sure to bring LOTS of popcorn lol
Sir, I don't usually comment but I just want to say thank you so much for putting this out on UA-cam for free. This was immensely helpful. Your hard work has not gone unappreciated!!
I am new to Power Query. I tackled almost all the new dynamic array formulas and some older formulas. Now I need to learn PQ, BI, and VBA. I have found PQ to be fairly easy for simple tasks and relatively straightforward for more complex situations. I have found if there’s something that you going to be doing multiple times, like changing field names, changing data types, etc, do as many as possible in one step. Delete all unnecessary columns before doing any further manipulation. Delete unneeded created columns all at once too
WOW! I got lost a couple (ok many) times and had to use the "J" key (to go back 10 sec ... smile), but that was on me for getting data and concepts from a "firehose" despite how wonderful with clear examples and caution on exceptions and possible pitfalls as you make presentations. Should be a requirement ... "ExcelIsFun certification" for every one that teaches online. Kudos for your selfless efforts!!! Just wish there was a "trial online testbed" for doing Power BI online exercises (or did I miss that).
Thanks for the kind words! I am glad that this class helps. Unfortunately, MS offers no such thing as free online Power BI practice. Stick to Power BI Desktop to learn the most important stuff: M Code, DAX and Visuals.
@@excelisfun TY for ack my Kudos. Actually I knew about no Power BI online ... I checked ... but thot, WTH, you might know of something. Also wanted the tone of my ending comment to be about your overreaching selfless capability and my need of more knowledge. TY again for setting a high bar for excellence. I know send $$ not compliments ... grin
@@mrdrk1ng I am good either way. Thoughtful comments that are insightful mean more than $$. I am much more of an artist than a businessman. I am more interested in creating the best story ever about a particular topic, than $$.
You are our Guardian Angel. I don't have enough words to express my gratitude to you. We are truly thankful for your hard work and dedication to educating the world. May God bless you with good health and prosperity. With love and respect. Arijit
Thanks Mike!!! I have just retired from work and this together with the Book, will keep me going for a long time not to mention upcoming future videos!! :) :) Everything in 1 video and in 1 book!!!! Amazing!!! How could we followers ever come to thanks you enough ?!?!?
Congratulations on retiring!!!! Time now to do nothing but Excel fun!!!! How can viewers thank me enough? Do just what you do: comment and thumbs up on each video. That is enough for me and it makes the Team complete : ) Thanks for being a great Teammate, Formula Guy John!!!
Astonishingly rich content (in the entire 365 collection, but this one is par excellence). Very motivating to work along using all the files. Thank you.
This was such an amazing video! Complete, fun, exceptionally well explained. I love the fact that you word your steps on each occasion, so they are easy to remember and elaborate despite the complexity of the subject. Thank you again Mike, you are really the best!
Extraordinary amazing! tanks for this updated version of older video series. I definitly watched the whole session not in a row but in about 5 parts. I enjoyed how you taught us through the topic.
Thank you for the kind words, Roger. No doubt that long videos are not for everyone, and they are hard to get through, but the advantage is that I can tell a story about all the tools and how they are interrelated without interruption - the full story, or as you say: "taught us through the topic"!!!
@ExcelIsFun Fully acknowledged! But there is a real advantage to to the student (the audience) as well. Everyone can get through at his/her/its own pace from the day of release. 👏
Prof. Mike you are great. You engrafted and endeared excel to me some years ago. You have never failed to amaze me with your free contents that would have cost a fortune to learn elsewhere. Thank you so much and God bless you.
Dear Sir , I have no words how to thanks to you. I have learned excel from you since my start of first Job. Thanks again for your effort, time and energy.
You know moct all of this. But the story is better than I have told before. You might consider picking some movie day or night, where you can watch it like a good action thriller lol
Your knowledge and wisdom just jewel for us, even be it older videos of new you just keep giving your awesomest best. I know there is no such word as awesomest, thank you Sir.
Thank you very much MIke, probably the most in depth and interesting data analysis video you've done,. I had to complete this in stages over a fortnight, but it will be invaluable to me in my career. I'd never done DAX formulas before, but you've made them look straightforward. You're a great teacher, thank you for educating us all!
Hi Mike, thank you for this tutorial. I just recently started to use power query and need to learn Power BI as a new requirement at work. This tutorial I already can see will make the transition so easy. Thanks a lot. This really means a lot.
You are welcome, Lester!!! Thanks for hanging out for many years here at excelisfun : ) I teach Excel in depth and Power BI intro. But, since Power Query and DAX formulas are in both tools, all the many videos about Power Query and DAX are applicable to both, but there are some intricacies to Power BI that I am not good with, so later you can pick up other details at other cool channels : )
@@atifsaleem7263 Yes, check out my list of cool UA-cam Channels here: ua-cam.com/users/ExcelIsFunchannels . The Power BI channel, Guy In A Cube and Curbal and a few good ones from the list.
Thank you again for your endless service to us Excel/Power Query/ Power BI geeks! You have helped so many of us stay on top of our skill-set! So grateful and I am so excited to dig into this😀.
@@excelisfun Thank you sir, please let me tell you something, I have been following you since 2012 and you have had a huge impact on my career. I don't know how to thank you but just know that you have inspired many people and I am one of them. I am from Tunisia, a country in North Africa and your reach is global. Thank you 🙏🙏🙏
@@Dexcelo I am so happy to hear your story, Dexcelo!!!! I am happy that I can have a positive impact on your career and many others from our Team : ) : )
Thanks for this epic video. Mike. So appreciated of your input that made Excel Data Analysis accessible to everyone. I am sure I will learn new things from this video.
Hi Mike, first of all thanks for this super jumbo video with all important topics in one! This is just a comment early on to tell you, how great is the job you have done! I will share final comments after completing the entire video as I am taking time out of my busy schedule and want to learn thoroughly & properly w/o hurry! 🙂
Thank you for the initial comment of support. We all have busy schedules, so you can come back to the data analysis fun, any time : ) I look forward to hearing hoe you like it later, Vijay Arjunwadkar!!!!
@@excelisfun Yes, I am! Visuals are the ones in the video: Matrix, Bar/Line Graph. Some unique slicers. Breakdown is similar: Location/Region, Year/Month, Type of Work
54:47 I found bit more chickier way for file updating. the Path in PQ has to be a text string. So I create either a named cell or small Table with 2 columns, Col1 is the path with & to concat it with cell2 in col2, then imported to PQ, in PQ i simply extract the path with file name from the table and convert it to basic text, then I simply reference that step (or query) in my main Power query, Now every time I need to change that specific file i simply type the new name into my excel table col2 and refresh the query, its small but it saves time and its more accessible to more ppl this way instead of editing the PQ input. Knowing that many people did not even heard of PQ that's the solution that's simpler and easier to grasp for majority of that file users. Also, This CSV file is also displayed on a web page. If the webpage is created "Correctly" it can be imported direct;y to power query using "From Web" import, and this makes downloading the CSVs unneccesery. 1:02:41 This is great PQ feature that auto merge, however this PQ created code is very computational heavy, so its extremly great when some1 starts to learn PQ (God only knows how many times Iused it when I was starting), However if a person is up for it, here: 1:02:01 I am advising a differnt Path, instead of clicking that button use Table.TransformColumn() formula and within it use Excel.Workbook(_) formula to convert all binary input into PQ Table object, next convert the whole column into a PQ List (simply at the formula's end after parenthesis add Column name in square brackets) and entagle the whole thing with Table.Combine() formula. This will greatly reduce the heavy calculation PQ is performing.
@@ExcelInstructor Yes, I am sorry, UA-cam/Google/Monopoly really makes things very difficult : ( The comment should not be too long, but if you can't post, just do what you have been doing and make a new posts : ) We all will benefit in that way for sure : )
You may already know a lot of this, Gareth, but the story I tell each time is hopefully a bit better - so the wading through the almost 4 hour video may be worth it!!!
@@excelisfun Wow didnt know about the formatted field name at the top, now it makes sense. Thank you Mike gonna take my time through this to soak up all the tips!
Hi Mike :) 27:30 Dont forget about CSV files with semicolon as delimiter, then excel not always properly splits the data, BUT power query will handle it correctly 29:09 small update for the folder path, I noticed "Desktop" in the path, With windows 10 or 11, not always the full path is displayed, Sometimes (Ithink it depends on windows group policy, I might be wrong), When you select Desktop instead of proper path C:\Users\\Desktop it will show only "Desktop" with a small monitor icon. Issue no 2: some people either by mistake or by lack of knowledge of how one drive work might simply click theu one drive setup linking Desktop / Documents / Download folder to onedrive memory. When this happens it will create some small issues with power query since PQ and VBA not always read One drive path correctly, which can create frustration The second issue I hope Microsoft will solve somehow - either in excel or in how onedrive is being viewed by other MS apps 31:50 I agree about using the ribbon commands when some starts or learns M code, However if that person will work more and more with power query it will get easier faster and even smarter to write their own M code which the ribbon would not be able to. Especialy with the basic group function which with Mcode knowladge can do true magick for any transformation or summerizing data. I truly do not want tobe taken as smug here, but the data I work with forced me to truly learn power query and to create some creazy M code. 41:06 I remember manualy changing column names... those were some dark times indead... now I use Table.TransformColumnNames function and with simple PQ if formula all is changed faster then manual re-typing
At the 2:01:56 minute mark, if anyone's "table" doesn't spill down, then move the "Measure sums to:" section over to the right a couple of columns and that will clear the way for the "table" to spill.
Thanks for helping the Team, John!!! I just jumped to the 2:01:56 minute mark and could not find the "Measure sums to:" section. What is the worksheet name with this mistake? I will
On the R DM DAX worksheet when doing the DAX calculations (this is section 12. Power Pivot, Data Model, Relationships, etc), my TotalSales was over 30,000, AverageSales was 380, and NumberTransactions was a little over 80,000. So, I looked at the source data, and saw that you had the additional data (starting at Row 43,246) already included in the table. I saw which row in the Table was the same as the starting row of the additional data, and deleted the data from that row downward, saved, and closed it. When I refreshed, the first 2 DAX calculations changed to the correct number, but the 3rd did not. So, I opened the source data again, and realized that I needed to delete those rows without deleting the extra data to the right of that table. So, I selected those empty cells at the end of the table, and deleted, saved, closed, and then refreshed. And that got the 3rd DAX calculation correct. The NumberTransactions calculation was seeing those blank rows of the table as transactions.
Oh boy I was searching for a comment like this because I was sitting for hours trying to solve why I was getting the wrong answer. Thank you @johnadair4979 ! His comment HAS to be pinned.
Two Notes: 1) Homework practice problems to the end of the workbook file: "04-M365ExcelClass.xlsx". 2) There is a Video Progress Checklist that can help to track progress through this long video. The "ProgressChecklist" worksheet is in the file "04-M365ExcelClass.xlsx" and is in a separate stand alone file named: "MECS04ProgressCheckList.xlsx". (Checklist idea came from Dom Tomoff at LinkedIn).
Hi Mike, I was wondering about excel and memory, Probobly soon I will start teaching excel in my work, so im wondering - is there any formula / addon / extension that would show much much memory specific cell input takes?
Just to visualy show that 1 takes less memory then "1" etc,
Do u know a way of measuring this?
@@ExcelInstructor Charles Williams Fast Excel Addin may be able to do that
@@excelisfun Thank you,
Im thinking about creating a video about combining files in Power query,
Could I refrence this tutorial?
@@ExcelInstructor Of course. I have many other videos, some even better than this one (more complete) if you want...
@@ExcelInstructor w
Folks, I've made an entire career using Excel with the help of these videos. This man is the best I've seen. Thank you for everything you do!!
You are welcome for everything, Mr. D!!!!!!
That's incredible! Can you please elaborate more on what you do and what would you recommend to someone who is a newbie?
@@SyedShadabHussaini1996 What I can say is I work for large auto manufacturers and occasionally make Excel dashboards. If I ever get stuck. I come here. Any of his base courses are gold. I'm very excited to watch this new one. That's all I can say. Best of luck.
@@SyedShadabHussaini1996 You can watch my Introductory video that teaches how to find videos and classes that can help you at my UA-cam channel: ua-cam.com/video/l1-1aVgFth4/v-deo.html
@@mr.d5050 WE ARE TWO.
This is a great course , and I am learning more and more every time
Glad you are liking it : )
I'm too broke to pay for online courses. Watched a lot of other videos on UA-cam, but they just beat around the bush.
Then I see this video. 4 hours straight to the point. No courses mentioned. No bs. Sir, you dropped this 👑
Glad you like it! This is what I have been doing for 15 years here at UA-cam: Free Education For The World!!!!
@@excelisfun That's awesome. Giving away so much knowledge for no cost where others are charging for the same knowledge is appreciated. Thank you so much.😊
@@AmolMY You are welcome so much : )
Hope your mom is feeling better Mike. Thank you for teaching the world how to unleash the power of excel!
There is a saying “Nothing comes free”. But you are proving it’s wrong. Your commitment in providing free Excel education to all is a matter of delight to interested students.
My goal here at excelisfun at UA-cam for past 14 years is to provide free Excel Education to the World!!!! I try to prove the statement “Nothing comes free” wrong, but I actually do charge students a thumbs up for watching and learning ; ) lol
*The Mike.... The Man.... The LEGEND!* 🏆🏆🏆
Thank you for your kind words, long time watcher D. Hall!!!!!
Thanks!
Boom 💥 💥 💥
Here we have it the mammoth episode, for which we were waiting for more than couple of week. And today only your book was delivered on my door.
Small way of saying thank-you for your work. Learning never ends with you.
Wow : ) : ) : ) : ) Thank you very much, Teammate Santosh Mekale: for helping to propel this excelisfun channel forward!!!!!
@@msantosh1220 Small is big : ) So glad this day gives you both mammoth video and book : ) : ) : ) : )
From one teacher to another:
You are the eighth wonder of the world
❤❤❤
Meni, that is very kind of you to say : ) Thanks : )
You know all the stuff in this video, Meni, did you watch the video anyway?
@@excelisfun
To be honest:
1. I don't know all the stuff in the video.
2. I haven't had the time to watch it in its entirety. It is very long and complex
("an epic video")
I'm writing now my first Excel book:
"120+ amazing tricks with the SEQUENCE function".
Some examples can be seen here, in a webinar I gave about a year ago on this extraordinary function.
You are kindly invited to watch in the following link:
ua-cam.com/video/gx9IGR2zsO4/v-deo.html
Enjoy 😎
Sir if i call you an excel wizard , it would be an understatement. The way you explain everything in depth , big fan ❤️.
You are significantly contributing to this world.
I am glad that my story telling can make the complicated less complicated.
Thank you so much Mike. How the world needs more people like you
You are welcome! And, yes, we can all do our part to make the world a better place : ) : )
I released video yesterday, Sunday. Today, Monday, I added some homework practice problems to the end of the workbook file: "04-M365ExcelClass.xlsx". But you will have to re-download zipped folder if you want access to them.
Mike Girvin belongs to the world for spending so much time and effort in creating many and useful Excel Is Fun videos. He is a treasure to the world.
Thank you for your kind words, brainy!!! I love bring fun data analysis to the world : )
Juste un type bien. Les cours sont parfaitement harmonisé. Même pour un francophone j'arrive à suivre parfaitement
Je vous en prie!
You are welcome!
Thanks
Thank you very much for your kind donation, Ajay : ) : ) : ) : ) The donation helps me to keep making videos for our awesome Online Excel Team.. Go Team!!!!!
Respected sir...you are raising the standard of education in your videos day by day... when we use these tricks in office ,people think us whizard also.... your teaching skill is better then the masters from whom even you learn..🙏🙏
Thank you very much, sujeet!!!! It is true, I am not the smartest about tools and methods (not one of those masters), but I do try to master the art of creating good stories that can make complicated things less complicated : ) : )
I've never commented on a UA-cam video before. But I wanted to let you know how much I appreciate your videos.
I also purchased your book and it's amazing. Thank you, Mike.
Thank you for making your first UA-cam Comment ever, Fernando!!!!! We actually have a lot of fun in these comments below my videos and share ideas and learn a lot. Welcome to the excelisfun comments!!! You are welcome for the vids and thanks for buying the book : )
Book name please?!
@@ahmednuru7698 Microsoft 365 Excel The Only App That Matters by Mike Girvin.
This Man knows what he says. He teaches with passion. 1 Million stars 🙂👏
Thank you for the Million Stars!!! : ) * Million!
Mike, when beautiful things come out of people, I often times wonder what was placed inside of them. I would enjoy hearing your story someday. Thank you for being here on this Earth.
Thank you for your kind words, Daniel!!! I am happy to be here on earth to try and make it a better place, fulfil my duties, have fun and get rad : ) That is how I think of being on earth. My story can be condensed down to: I follow my heart and do what I love; since all else follows from love, the hard work, dedication and perseverance is automatic; then I can have fun with construction stories about Excel and Data Analysis to try and make complicated things less complicated; and occasionally get rad with BMX, skateboarding and boomerangs.
@@excelisfun Well said Mike!
@@danielhutchinson6158 : )
@@excelisfun I finally made it to the Power BI section of this video. So grateful!
@@danielhutchinson6158 Yes!!!!! We are so lucky to have so many wonderful data analysis Microsoft tools : )
This is priceless treasure who have been hunting for excel learning guide 👏👏🙏🙏🙏🙏
I hope the Excel and Power BI treasure will guide you in a great direction, Virtual!!!
I should call it the HOLY BIBLE OF EXCEL... I have gone through umpteen videos but never ever came across such an effortful, useful, and easy to understand tutorial on the subject. I thank you from the bottom of my heart for bringing this video... God bless you 🙏
You are welcome from the bottom of my heart!!! This is what I have been doing for 15 years at UA-cam: Provide full free education that is telling stories to make complicated things less complicated : )
Awesome as always, MECS rocks!
Thank you for your kind donation, rigov!!!! I am glad that you enjoy the MECS!!!! : )
What a fabulous video. If you only watch one Excel video in your life, watch this one!
Yup. ; )
Good explanation 👍
Thank you for your kind donation, Excel (A-Z)!!!! What are some of the useful things that you took away from the video? I see you are an Excel UA-cam Teammate : ) I just subbed to your channel!!!
Never had to comment on UA-cam. Mr. Girvin has to gift to teach, to connect with students along with the ability to explain very clearly.
He purchased the latest book just because the author is, Mr. Mike Girvin.
Thank you for being able to help.
You are an outstanding teacher, and you got me hook on UA-cam, but now I will focus on that great book that you put out there for us.
Respect
Thank you for the respect, Aliou!!!! I am glad you like the clear and fun videos!!!!!
Mike, I’ll watch this in stages, but as always thanks so much for all you do to make the Team better!
That is soooooo true, Chris M. Too much to take in for one video for most people. BUT, in your case, you know and have seen most of the examples that I show, and since this is a different story and a much better story about Data Analysis tools than I have told before, you could actually watch it like a 3 hour 44 minute movie on Saturday night - just be sure to bring LOTS of popcorn lol
Definitely LOTS of popcorn!
@Excellsfun - Man I follow your channel for last 8 years and I respect your hard work. Amazing bro, keep it up
I never knew data could be so fun! What a time to be alive!
Fun and power to make smart decisions : ) : )
FINALLY!!!. Haven't even started watching. But I'm so excited. Thanks Mike
You get the first place trophy for first comment, Temidayo!!!!
Sir, I don't usually comment but I just want to say thank you so much for putting this out on UA-cam for free. This was immensely helpful. Your hard work has not gone unappreciated!!
You are welcome so much, Isitha!!!! Data Analysis is fun ; )
I am new to Power Query. I tackled almost all the new dynamic array formulas and some older formulas. Now I need to learn PQ, BI, and VBA.
I have found PQ to be fairly easy for simple tasks and relatively straightforward for more complex situations.
I have found if there’s something that you going to be doing multiple times, like changing field names, changing data types, etc, do as many as possible in one step. Delete all unnecessary columns before doing any further manipulation. Delete unneeded created columns all at once too
I am so glad that you have discovered the fun and power of Power Query, Patrick!!!!
Mike, you are awesome. Thanks for this incredible content.
You are welcome !!!!!
A great teacher like you leads the great future for your students! 😊 Thank you for everything!
You are welcome for everything!!!!!
This is a brilliant video if your learning power bi - I’m fairly handy at power bi now but I still picked up quite a few tricks watching this.
Glad you found a few tips : )
WOW! I got lost a couple (ok many) times and had to use the "J" key (to go back 10 sec ... smile), but that was on me for getting data and concepts from a "firehose" despite how wonderful with clear examples and caution on exceptions and possible pitfalls as you make presentations. Should be a requirement ... "ExcelIsFun certification" for every one that teaches online. Kudos for your selfless efforts!!! Just wish there was a "trial online testbed" for doing Power BI online exercises (or did I miss that).
Thanks for the kind words! I am glad that this class helps. Unfortunately, MS offers no such thing as free online Power BI practice. Stick to Power BI Desktop to learn the most important stuff: M Code, DAX and Visuals.
@@excelisfun TY for ack my Kudos. Actually I knew about no Power BI online ... I checked ... but thot, WTH, you might know of something. Also wanted the tone of my ending comment to be about your overreaching selfless capability and my need of more knowledge. TY again for setting a high bar for excellence. I know send $$ not compliments ... grin
@@mrdrk1ng I am good either way. Thoughtful comments that are insightful mean more than $$. I am much more of an artist than a businessman. I am more interested in creating the best story ever about a particular topic, than $$.
You are our Guardian Angel.
I don't have enough words to express my gratitude to you.
We are truly thankful for your hard work and dedication to educating the world.
May God bless you with good health and prosperity.
With love and respect.
Arijit
Thank you for your kind words and love, Arijit!!!!! I am glad you like it all : ) : )
I had an assignment in data analytics but had no idea where to start from. This video is by far the best.
Definitely the most complete free resource in the world : )
You are great my friend. Your energy throughout the video is really amazing. God bless.
Glad you like it!!!
Thanks Mike!!! I have just retired from work and this together with the Book, will keep me going for a long time not to mention upcoming future videos!! :) :)
Everything in 1 video and in 1 book!!!! Amazing!!! How could we followers ever come to thanks you enough ?!?!?
Congratulations on retiring!!!! Time now to do nothing but Excel fun!!!! How can viewers thank me enough? Do just what you do: comment and thumbs up on each video. That is enough for me and it makes the Team complete : ) Thanks for being a great Teammate, Formula Guy John!!!
Astonishingly rich content (in the entire 365 collection, but this one is par excellence). Very motivating to work along using all the files. Thank you.
You are welcome for the rich content and fun files, Z T!!!!
You helped Us a lot i learned 90% excel from you now my friends asks me to help in excel .
I am glad to help you and I am very happy to hear that you help your friends now : ) Now that is fun with Excel, Amit!!!!
This was such an amazing video! Complete, fun, exceptionally well explained. I love the fact that you word your steps on each occasion, so they are easy to remember and elaborate despite the complexity of the subject. Thank you again Mike, you are really the best!
You are welcome : ) : ) : )
Thank you Sir 👍👍👍
I learn a lot from all of your video tutorial.
It help me many times.
I am glad to help you many times!!!!!
Give this man a medal!
Thanks for the medal, Adwaite!!!!!
Extraordinary amazing! tanks for this updated version of older video series. I definitly watched the whole session not in a row but in about 5 parts. I enjoyed how you taught us through the topic.
Thank you for the kind words, Roger. No doubt that long videos are not for everyone, and they are hard to get through, but the advantage is that I can tell a story about all the tools and how they are interrelated without interruption - the full story, or as you say: "taught us through the topic"!!!
@ExcelIsFun Fully acknowledged! But there is a real advantage to to the student (the audience) as well. Everyone can get through at his/her/its own pace from the day of release. 👏
@@RogerStocker That is very true. I appreciate a VERY Smart an Awesome Excel Expert like you taking the time to watch it all : )
Prof. Mike you are great. You engrafted and endeared excel to me some years ago. You have never failed to amaze me with your free contents that would have cost a fortune to learn elsewhere. Thank you so much and God bless you.
You are welcome for the free content!!!! But I do charge a thumbs up ; )
Wow this must be the Ben-Hur of excel tutorials. Many thanks Mike.
Cool comment: Ben-Hur of excel tutorials!!! Love it, Mark : ) : )
This Comment is for Myself!!-- 3:08:00 creating Date Table explained really well :)
Yes!!!!!
Thank you so much! I just wanted to learn some basics so I could do a project at home, and you've been so helpful with these videos.
Dear Sir ,
I have no words how to thanks to you. I have learned excel from you since my start of first Job.
Thanks again for your effort, time and energy.
You are welcome, Atif!!! i am very happy to help you : )
Wow..kool Video...
Eda 01-04 series was super kool...This is at another level..
Thank You Mike for this comprehensive video...
Yes, this is a new level of telling complete stories about Excel and Power BI Data Analysis : ) It's more fun too : ) : )
I say nothing but long live to Mike. We love you much!
Thanks for the love : ) : ) : ) : )
Thanks! Just got notified. Its a BIGGIE! LOL. My schedule only allows me about 45 min to an hour a day for U-Tubbing!! So it will take a while!!!
You know moct all of this. But the story is better than I have told before. You might consider picking some movie day or night, where you can watch it like a good action thriller lol
@@excelisfun Get back to you later!! : )
@@richardhay645 Like a boomerang ; )
Absolutely Amazing … as usual from ExcelisFun Channel … what an effort in just one Video …. Thanks a lot Mike
You are welcome a lot Hussein!!!!
Wow 🤗... This is so amazing.. you should be celebrated sir... Lots of loves 💕
Glad it is amazing, Omotosho, and thanks for the love!!!!
Your knowledge and wisdom just jewel for us, even be it older videos of new you just keep giving your awesomest best. I know there is no such word as awesomest, thank you Sir.
You are welcome, Ajay!!! Your words and awesome and I love them : )
Thank you very much MIke, probably the most in depth and interesting data analysis video you've done,. I had to complete this in stages over a fortnight, but it will be invaluable to me in my career. I'd never done DAX formulas before, but you've made them look straightforward. You're a great teacher, thank you for educating us all!
You are welcome!!! I definitely tried to make this the one-stop shopping video for Microsoft Power Tools For Data Analysis.
Hi Mike, thank you for this tutorial. I just recently started to use power query and need to learn Power BI as a new requirement at work. This tutorial I already can see will make the transition so easy. Thanks a lot. This really means a lot.
You are welcome, Lester!!! Thanks for hanging out for many years here at excelisfun : ) I teach Excel in depth and Power BI intro. But, since Power Query and DAX formulas are in both tools, all the many videos about Power Query and DAX are applicable to both, but there are some intricacies to Power BI that I am not good with, so later you can pick up other details at other cool channels : )
@@excelisfun Dear Sir,
can you please suggest any Channel for Power BI.
Thanks in Advance.
@@atifsaleem7263 Yes, check out my list of cool UA-cam Channels here: ua-cam.com/users/ExcelIsFunchannels . The Power BI channel, Guy In A Cube and Curbal and a few good ones from the list.
Thank you again for your endless service to us Excel/Power Query/ Power BI geeks! You have helped so many of us stay on top of our skill-set! So grateful and I am so excited to dig into this😀.
You are welcome, IreneH!!!! I hope that the dig will yield good nuggets : )
Mike, thanks again for the video! Thanking you even before watching it, because I know it definitely is epic! May you stay blessed SIR! 👌
You are welcome for the epic, Shehan!!!!
Extremely useful lesson. Thanks Mike
So glad to help, Alex!!!!!!
A very big LOVE YOU from EGYPT! u r the best one ever presented Excel
Thanks for the love! Glas you like the material : )
This was great !! the attention to explaining the details is mesmerizing.
I hope the mesmerizing details will stick, Dexcelo!!! If not, the video isn't going anywhere : )
@@excelisfun Thank you sir, please let me tell you something, I have been following you since 2012 and you have had a huge impact on my career. I don't know how to thank you but just know that you have inspired many people and I am one of them. I am from Tunisia, a country in North Africa and your reach is global. Thank you 🙏🙏🙏
@@Dexcelo I am so happy to hear your story, Dexcelo!!!! I am happy that I can have a positive impact on your career and many others from our Team : ) : )
Again😀 Great Contents. Really Compressed many Idea in my Videos.
I am glad that you liked all the ideas in one video, Sanjeev!!!!
Wonderful explaination, A lot of effort goes into teaching every tool . Hats off and thank you very much Sir
That's awesome Professor! I owe you my career. Many thanks ;)
Many your are welcomes!!!!
sir please more videos on power bi.you are really the hero of excel
Amazing, Outstanding, Excellent! Best regards from Limón, Costa Rica!
Glad this helps!!!!
Thanks for this epic video. Mike. So appreciated of your input that made Excel Data Analysis accessible to everyone. I am sure I will learn new things from this video.
You are welcome, for the epic data analysis fun, KERRY!!!!
When I am depressed or feeling bad I start to watch your videos
Really nice course! Literally loved every minute of it, as you explain things very clearly thanks from somalia
You are welcome!
Hi Mike, first of all thanks for this super jumbo video with all important topics in one! This is just a comment early on to tell you, how great is the job you have done! I will share final comments after completing the entire video as I am taking time out of my busy schedule and want to learn thoroughly & properly w/o hurry! 🙂
Thank you for the initial comment of support. We all have busy schedules, so you can come back to the data analysis fun, any time : ) I look forward to hearing hoe you like it later, Vijay Arjunwadkar!!!!
Woooow.It is a wonderfull. All in one place. Lots of hard work with a kind heart. Thank you Sir. Really really important facts.
Hard work and kind heart are fun ; ) Hope you will like it, Priya!!!!!
The password joke at 2:36 got me. Nice one!
Glad it was fun, Daniel!!! The joke was not at 2:36, but I think I remember it when I recorded this 2 months ago. What minute mark was it?
@@excelisfun I believe it was at 2hr and 36min (156min.). About to publish my first report with PowerBI at the end of this week!
@@danielhutchinson6158 02:36:34 is funny password joke.
@@danielhutchinson6158 Are you publishing it through your organization? If yes, that is exciting!!! What sort of report and visuals are you doing?
@@excelisfun Yes, I am! Visuals are the ones in the video: Matrix, Bar/Line Graph. Some unique slicers. Breakdown is similar: Location/Region, Year/Month, Type of Work
The wait is over... Rad video 🖐️
Yes, the wait is over, Ankur!!! I like your description: Rad Video ; )
Great one sir!
Glad you like it : ) Good teaching is all about story telling : )
Epic video is released!!! I'm sure i will enjoy it. Thank you very much Mike.
Take you time. Have fun ; )
54:47 I found bit more chickier way for file updating. the Path in PQ has to be a text string. So I create either a named cell or small Table with 2 columns, Col1 is the path with & to concat it with cell2 in col2, then imported to PQ, in PQ i simply extract the path with file name from the table and convert it to basic text, then I simply reference that step (or query) in my main Power query,
Now every time I need to change that specific file i simply type the new name into my excel table col2 and refresh the query, its small but it saves time and its more accessible to more ppl this way instead of editing the PQ input. Knowing that many people did not even heard of PQ that's the solution that's simpler and easier to grasp for majority of that file users.
Also, This CSV file is also displayed on a web page. If the webpage is created "Correctly" it can be imported direct;y to power query using "From Web" import, and this makes downloading the CSVs unneccesery.
1:02:41 This is great PQ feature that auto merge, however this PQ created code is very computational heavy, so its extremly great when some1 starts to learn PQ (God only knows how many times Iused it when I was starting), However if a person is up for it, here: 1:02:01 I am advising a differnt Path, instead of clicking that button use Table.TransformColumn() formula and within it use Excel.Workbook(_) formula to convert all binary input into PQ Table object, next convert the whole column into a PQ List (simply at the formula's end after parenthesis add Column name in square brackets) and entagle the whole thing with Table.Combine() formula. This will greatly reduce the heavy calculation PQ is performing.
You have so many great tips, my Rad friend!!! Maybe you can edit the comment that I pinned to the top, so that more people will see them : )
@@excelisfun I tried, twice already, UA-cam wont let me - maybe the comment got too long? I dont know, thats why I created second comment.
@@ExcelInstructor Yes, I am sorry, UA-cam/Google/Monopoly really makes things very difficult : ( The comment should not be too long, but if you can't post, just do what you have been doing and make a new posts : ) We all will benefit in that way for sure : )
@@excelisfun yeah, it simply states "Returned Error" when I try to edit my comment, So I do not even know whats the Error.
Mike is on fire!!! you are the king man, love u so much!!!
Yes!!!! Fire!!! Thanks for the love, WaitPlan Wp!!!!!
Great job Mike.
Glad you like it, Sergio!!!!
Boom!It's Gonna Take Me A Few Sittings To Enjoy This Mega Awesome Class...Thank You For All The Fun Mike :)
You are welcome for all the fun sittings, Bike Brother darryl!!!!!
Thanks sir. It is really epic and covers all the regiona. Thanks again for your precious time
You are welcome for the precious Data Analysis fun, m kodan!!!!
As advertised, amazing video!
Yes!!!!! No fake ads here ; ) Glad it is amazing for you, Dawson!!!
@@excelisfun It was amazing! I know you are probably super busy but when and what can we expect for the next video like this?!?!
@@dawsonporter9857 The next two MECS will have specific topics like Date & Time, Logical Formulas & Conditional Calculations.
@@dawsonporter9857 I hope to have one out each week... But my plans never go as planned : (
Totally completed this lesson. I have a bulk ideal after this. Thank you alot. I,d like give more than one thumb up icon but it not allow. Kkkk.
Thanks for the one thumbs up, Khai!! Glad this long video helped : )
You are amazing! This lesson so good so far and quailified detail.
Yes, it is VERY detailed - might take watching many times. But at least it is one easy to find video ; )
This is the epic one. Thank you for making and sharing this Mike! Will take in over some short bursts.
You may already know a lot of this, Gareth, but the story I tell each time is hopefully a bit better - so the wading through the almost 4 hour video may be worth it!!!
@@excelisfun Wow didnt know about the formatted field name at the top, now it makes sense. Thank you Mike gonna take my time through this to soak up all the tips!
@@garethwoodall577 There are so many, many tips all in the correct order for a good "how to do data analysis in Excel and Power BI" : ) : )
Hi Mike :)
27:30 Dont forget about CSV files with semicolon as delimiter, then excel not always properly splits the data, BUT power query will handle it correctly
29:09 small update for the folder path, I noticed "Desktop" in the path, With windows 10 or 11, not always the full path is displayed,
Sometimes (Ithink it depends on windows group policy, I might be wrong), When you select Desktop instead of proper path C:\Users\\Desktop it will show only "Desktop" with a small monitor icon.
Issue no 2: some people either by mistake or by lack of knowledge of how one drive work might simply click theu one drive setup linking Desktop / Documents / Download folder to onedrive memory.
When this happens it will create some small issues with power query since PQ and VBA not always read One drive path correctly, which can create frustration
The second issue I hope Microsoft will solve somehow - either in excel or in how onedrive is being viewed by other MS apps
31:50 I agree about using the ribbon commands when some starts or learns M code, However if that person will work more and more with power query it will get easier faster and even smarter to write their own M code which the ribbon would not be able to. Especialy with the basic group function which with Mcode knowladge can do true magick for any transformation or summerizing data.
I truly do not want tobe taken as smug here, but the data I work with forced me to truly learn power query and to create some creazy M code.
41:06 I remember manualy changing column names... those were some dark times indead... now I use Table.TransformColumnNames function and with simple PQ if formula all is changed faster then manual re-typing
Hot tips for everyone!!!!!
@@excelisfun I hope you dont mind?
@@ExcelInstructor O no : ) : ) I love your hints. We are a Team and together we know much more.
@@excelisfun Thank you :)
You can post your other comments as comments below this comment, if you want : )
At the 2:01:56 minute mark, if anyone's "table" doesn't spill down, then move the "Measure sums to:" section over to the right a couple of columns and that will clear the way for the "table" to spill.
Thanks for helping the Team, John!!! I just jumped to the 2:01:56 minute mark and could not find the "Measure sums to:" section. What is the worksheet name with this mistake? I will
O, I just found it. I will fix this and re-post workbook so that your insight can help the Team : )
@@excelisfun FilterContextVisual. Thanks.
@@excelisfun I saw this post after I replied to your first. lol
I have not viewed it yet, but for sure this video is another masterpiece from Mike :) Thanks Mike for your HUGE contribution to the world
It does have a lot of data analysis tips for Excel and Power BI. You are welcome, Phan Dang!!!
This is fabulous!! I've just started to watch your video, downloaded your file, thank you very much!! a thousand likes
I shopped one time. Great video.
I love it: one time shopping is fun : )
I just finished this epic video. It's amazing. Dancing ant GIF made this video more fun. 👯
Yes, now we have fun Ghosts and Dancing Ants : ) : ) : ) : )
On the R DM DAX worksheet when doing the DAX calculations (this is section 12. Power Pivot, Data Model, Relationships, etc), my TotalSales was over 30,000, AverageSales was 380, and NumberTransactions was a little over 80,000. So, I looked at the source data, and saw that you had the additional data (starting at Row 43,246) already included in the table. I saw which row in the Table was the same as the starting row of the additional data, and deleted the data from that row downward, saved, and closed it. When I refreshed, the first 2 DAX calculations changed to the correct number, but the 3rd did not. So, I opened the source data again, and realized that I needed to delete those rows without deleting the extra data to the right of that table. So, I selected those empty cells at the end of the table, and deleted, saved, closed, and then refreshed. And that got the 3rd DAX calculation correct. The NumberTransactions calculation was seeing those blank rows of the table as transactions.
Thanks for helping with my errors. What is the worksheet name with this mistake? I will fix it.
@@excelisfun On the R DM DAX worksheet.
@@johnadair4979 O, I see. That is a picture of the answer.
Oh boy I was searching for a comment like this because I was sitting for hours trying to solve why I was getting the wrong answer. Thank you @johnadair4979 ! His comment HAS to be pinned.
You are a great teacher! Thank you for this lesson :)
You are welcome!!!
from france thank you for this great video
Wait till you see that last example in this video (map example) where I talk about the cool things happening in France right now : )
Greatest video... Thanks... 🙏
You are welcome, Dr.Vaibhav!!!!
This is a Masterpiece!
I hope so, O Awesome Teammate Excel Lambda!!!!
Cảm ơn bạn!
Thank you very much for your kind super thanks : ) : )
thanks so much. You are truly a teacher
You are welcome for the teaching !!!
I DIED when you made the dot password joke at 2:36:43 😂😂😂😂 First time I ever heard that dad joke
Yes!!! Laughing while doing Excel and Power BI makes things much better!!!