Introduction to Power Query & Power Pivot Data Model in Excel 2016 (Excel Magic Trick 1468)
HTML-код
- Опубликовано: 26 июл 2024
- Download Excel File #1: excelisfun.net/files/EMT1468.... Download Excel File #2: excelisfun.net/files/EMT1468F...
Download Zipped Folder: excelisfun.net/files/EMT1468T...
Entire page with all Excel Files for All Videos: excelisfun.net/
Buy excelisfun products:
teespring.com/stores/excelisf...
Learn about how
Topics:
1. (00:16) Introduction
2. (00:51) Discussion about the two different Data Ribbon Tabs and Get & Transform groups that you may see in Excel 2016
3. (03:51) Example #1: Clean Data and create Excel PivotTable Report, then add new data and refresh report!
4. (17:06) Example #2: Transform Cross Tabulated Table into Proper Data Set
5. (21:50) Example #3: Import 1.2 Million Rows of Data from Text Files into Power Pivot and create a Data Model PivotTable, then add new data and refresh our report.
6. (22:34) Diagram to understand Data Model and Power Pivot
7. (46:46) Unhanded Exception Error.
8. (48:02) Summary
Reference Video:
Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model
• Highline Excel 2016 Cl...
![Cordae - Saturday Mornings (feat. Lil Wayne) [Official Music Video]](http://i.ytimg.com/vi/z2GYJua09F0/mqdefault.jpg)
I worked with Excel for years and was never confident about it. I started watching your videos two months ago and now I am able to do so much I just can’t stop trying to improve all my processes. I’m in awe with your gift for teaching. Thank you so much for giving all this for free.
Glad you like it and glad that it helps you - but its not free... I need your help with Thumbs Ups on each videos, comments when you are inspired and a sub : )
Dude just 4 months ago, i am the guy in the office who dont know E of excel and now I am taking excel sessions in office.
@@excelisfun I want to level up my use of excel. Just subscribed but you have so many videos! Can you point me as to where I should start? I'm a basic excel user.
@@Tanus912 Yes I can!! And I have JUST the perfect class. Here are three classes, all in succession:
Excel Basics class: ruclips.net/p/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Advanced Excel: ruclips.net/p/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
Power Tools: ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1
Please help support on each video with a small comment and Thumbs Up : )
Not only do I give thumbs up, I have told everyone to go watch your videos and learn something.
my first thought - "this is way too structured and polished to be free."
But who am i to question a miracle.
Thank you!!
That is what I have been doing for over 13 years at RUclips: Free Excel Education for the world : )
Thank you for sharing all this gold with us!
Introduction to gold!!! That is very cool, Leila : )
Leila still my Champ
I was thinking the same thing ....gold, where has this video been after 4 weeks? GOLD!
@@marcoalcovendas4476
Leila is absolutely great but so is Mike Girvin from Excellsfun channel 👏🏻👏🏻👏🏻. We are fortunate to have them available through RUclips!
@@excelisfun 5
Idk what nation you're from but you're their national treasure because holy cow this is finally making sense, thank you so much!
I am so glad to help! My goal is to provide free education to the world. As you say, it doesn't matter where I am from, it just matters if I try and make the world a better place : )
Thanks for your support on each video that you learn from with a comment and thumbs up, and of course your Sub : ) Those help me to further the goal of free education to the world : ) : )
Wow Mike. I'm so behind in your tutorials. Haven't checked your page lately but you know that every time I get into trouble with Excel it's always your page that I check out for answers. Thanks a million!
Mike, I can't thank you enough! Really appreciate the downloadable files to be able to practice on. I've been unemployed for a few months and your content is helping me learn Excel. If you have a Udemy course, I'd gladly pay for it.
Mr Mike, What ever we said it is not enough....Keep in my that we re Thankful every minutes and second...I just cancelled all other RUclips Channels and lessons, and only with your helpful videos which were made in an amazing way with a lot of patient to be organized in this way....God Bless you
I am glad that what I make can help you and others, huda!!!!
Oh my god. I bloody love how comprehensive and detailed this was! Thank you so much.
That is my specialty: full free education, all the details in a fun way. Thanks for your support on each video that you learn from with a comment and thumbs up, and of course your Sub : )
You're an amazing teacher, thank you. First time in a long time when I actually got excited about something. Always good to find new ways how to be lazier at work! :)
I love it: exciting and lazy at work : ) You are welcome for the vid! You can help me each time you watch with a Thumbs Up and short comment : )
I’d watched so many excel coaching videos before I found your channel.... you are hands down the best Excel coach!
Glad the coaching helps, Grace!! Thanks for your kind words!
I've learned so many new things in this video, I finally got to use the Power Pivot tab and the Data Model do something with it! I'm feeling a bit more confident now that I've done the related homework from the Advanced Course. I'm looking forward to the next video. Thank you again Mike.
Late starter to power query, this tutorial was mind blowing. Love it. Thanks Mike!
This is by far the best explanation i have ever seen of Excel Power Query, Pivot and Data Model. Wow!! Incredible work!! Thank you so much!! Please make another similar video with the latest updated Excel.
Here is a 4 video playlist with all the latest: ruclips.net/p/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp
Great video as always, Mike.
If I may, 15:05, in order to easily incorporate new data into the table, copy the new data to be inserted, select the bottom right cell in your table, press TAB (which will jump to the next row, but WITHIN the table), then Paste. Voila!
Thanks for that hot tip! I had my Excel Options so that new data would not be included... I should have just adjusted that so this issue would not occur.
Hi Mike.. so excellent. I've watched this numerous times and get something new out of it with each viewing. Thanks this great introduction to PQ and the Data Model. Thumbs up!
Amazing free course! Thanks Mike!!!
You are welcome!!! : )
@@excelisfun ,God bless
You are so amazing. Your presentation is the most detailed I have seen that explains Power Query and Power Pivot in the most simplified way. This an absolute 5 * rating
Glad you like this, Gold Agharese!!! Thanks for the 5 stars : ) There are many other videos like this for you to learn from ta the excelisfun channel!
Who knew that ExcelIsFun? Thank you for an interesting presentation on the topic. Not boring, to the point, useful.
I learned so much just by watching 10 mins of this video, let alone the entire 50 mins. So glad I found this channel, your enthusiasm just makes the video a whole lot better. I will definitely be checking out your other videos.
Thank you so much, all the way from Brazil.
You are welcome, Leonardo, from up here in Seattle, WA, USA. There are many more videos. If you need help finding a specific one, let me know. And please help me to continue providing free education to the world with your comment and thumbs ups on each video that you learn from, and your Sub too : )
This video has changed my life!!! Honestly just solved so many of my problems. I'm converted; Power Query for life!
Glad it changed your life! Thanks for the support with your comment, Thumbs Up and Sub : )
Amazing introduction! I am so excited that Power Pivot and Power Query can do so much!
These features are awesome! Mike, you have the unique ability in explaining the many useful features of excel in a very simple yet clear manner. Subscribed to your channel a few years back and now, I am able to impart my excel knowledge to my officemates and friends. You are such a great resource and truly a wonderful person. Many thanks.
You are welcome, Han! Thanks for your support with your comment and thumbs up on every video that you learn from, and of course your Sub : )
I am so impressed with Mike. His videos are always awesome! Thanks Mike!
WOW!!! I have to learn and practice this because I’m MANUALLY managing data... THIS will give me back about 40hrs a week down to about 10 mins. AMAZING! Thank you!!!
Thank you so much for your time. Not only you show how to use PQ, but also shows some good tricks and Tips.!!!!!
You are welcome!
You have no idea how much time this knowledge can save us. I have a colleague who spends 3 days/month transforming database exports into Excel to create pivot tables. I think with 30 minutes of work, we can get the 3 days down to 3 minutes. This is a massive improvement in productivity.
I have been watching your videos for 3 months now. You are a remarkable teacher. I cannot express my gratitude for all of the videos you post relating to power query and power pivots. For a new hire out of University you have helped define my role in the workplace and enable me to standout amongst my peers in only a short period of time.
You are welcome, Eidan!!! You can express your gratitude with a Thumbs Up and comment on each video, Sub and tell all your friends : ) Congratulations on taking the material and standing out amogst your peers : )
MS excel is an everyday thing for me. it's a part of my life and profession. I've been watching, following through your excel videos and and this has really helped in enhancing my skills, now PQ and PP. I listen to u every now and again as my user manual. Lessons are in bite sizes and easy to assimilate. You just made my life easy as an Accountant and analyst. You met more than a need. Thank you for all the video trainings you did for accountants. I can't thank u enough but thank you for sharing so much.
Respect!!!
You are welcome for the free education! But you can thank me enough by commenting and clicking thumbs up on every video that you learn from.
Hello! I have been trying to make a new class material for this subject for many months until I found your video yesterday. Thank you for having me understand the subject finally to full extent and vigor. Without you and RUclips, perheps more of my time would have been wasted. What a dedicated service!
You are welcome, Yong Jae Kim!! Thank you for helping to support this few resource with comments and Thumbs ups on each video that you watch, and Sub too : )
You are an amazing Teacher. God bless you brother. Wow this video is a big help to my work
This is the best starter tutorial I've seen for Power Query & Power Pivot yet. Top work!
Glad it helps, Michael!!! Thanks for the support with your comment, thumbs up and Sub : )
Excel Master Mike in action!!! Fantastic video tutorial!!!
Thank you O Power Query Masters of Masters!!!! And... P.S. A Poet and Artist with PQ too : )
I have full respect for you both :)
The unpivot command is a revelation. This whole tutorial is very informative and will be extremely useful in my new role. Thank you very much.
Sir no words find , no idea which word is best , no idea how thank you, on this effort, but i wanna salute you, My heart always prayer for you, sir please don,t leave us , you know you have family on you tube , this family love every time , one again thanks sir
I am happy to help. It is my duty as a human to try and make the world a better place, and this is one way : )
You are just too good.. Fantastic you just made my day with your power presentation. Wow!
Glad it helped!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Mike, you're such an amazing teacher! I can't thank you enough for all that you do but I will try - THANK YOU SO VERY MUCH! :)
You are welcome so much, Gaya!!!
A nice comprehensive review of the basics. Thanks.
You are welcome!
You Sir, truly deserve a medal.
Amazing tutorial with so useful guideline and explanations !
Glad it helps! That's why I post. Thank you for the support with your comment, Thumbs Up and Sub many more videos to come : )
Wow! That was an amazing video! Thank you so much! I had no idea Excel 2016 could do all that. Very well explained and visualized. I will definitely use all of these features at work.
You are welcome! I am glad that it helps! Be sure to support the channel with Thumbs Up and comments on each video that you watch and Sub for lots more videos. Although for the next few months I am releasing a Basic Math and Excel series, in spring I have a full series of videos on just Power Query and Power Pivot.
Cannot thank you enough. I've always considered myself an advanced excel though only now do I have to deal with enormous data sets. In all of a week your videos have guided me in becoming very comfortable with power query and power pivot. Presentation is fantastic, you Emphasise the right steps perfectly. Keep up the good work. Now onto power bi for me!
You are welcome! That is why I post - but be sure to help our this free resource with those Thumbs Ups, a Sub and your awesome comments : )
Fantastic. I've used Power Query for working with Excel tables, but never outside data files like this. Opens the mind to possibilities!! Thanks Mike!
Awesome! Yes Power Query is THE most amazing thing invented in Excel since the PivotTable back in 1990s. Thanks for your support with comments and Thumbs Up and Sub : )
God bless you!! What a gift you are to the world!!! Thank you for your generous spirit and love of teaching!!
You are an awesome person. People would charge a lot on online learning platforms and produce crappy content. But providing such a solid comprehensive course for free, hats off sir! Thank you so much.
Yes, for 12 years here at RUclips, that has been my goal: Free Education For The World : ) Thanks for stopping by in the comments, Niki, and thanks for the thumbs up and Sub : )
ExcelIsFun I’m also a fan of your book Ctrl Shift Enter :)
@@NikVilluri Yes, even though it was written 8 years ago, it still has so many fundamental formula tricks to help us succeed. Thanks for your support, Nik : )
I appreciate you taking effort to nicely explain these not so easy concepts. When any of my friends or family wants to learn more about MS Excel, I ALWAYS recommend your channel.
Thanks for the recommendations. I am so glad I can help you and your friends : ) Thanks for your support, phn : )
I've enjoyed your video, it is a little fast past and had to watch it a second time! but wow, really great info on power query and power pivot tables. Watching all the way from Ciudad de México!
This was such a find. I've spent hours building a VBA macro to unpivot some very substantial official government published pivot tables so that I can combine data and create new pivot tables from the combine data of numbers of unpivoted files. This does it soooooooooooooooo effortlessly. Thanks for sharing your knowledge
Yes indeed - Power Query is the biggest game changer since the PivotTable back in the 1990s. Be sure to watch more about Power Query at my channel. I just posted a new series all about Power Query. And thanks for the support with your comment, Thumbs Up and Sub, Francis : ) Here is the Power Query Playlist: ruclips.net/p/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK
Absolutely amazing - with explanation.. Thanks for such a wonderful video tutorial..
It is amazing. I know I am commenting a year after but I just came across this video while I wanted help from a paid video tutorial that I was watching about power query and trust me you are the most elaborate explainer I have ever come across plus the practice files you linked are amazing and hats off to your commitment and dedication. God bless you. I have subscribed and hit the bell icon. I am awaiting your next videos. see you soon with a new video.
I am so glad that you find useful the materials that I post, CADSpecialist! Thanks for your support, and I can't wait to post the next one for you!!! Don't forget, there are 3000+ videos already posted that you can search by topic when you go to the excelisfun RUclips home page: ruclips.net/user/ExcelIsFun
Thank you for finding time and sharing this to us viewers. This means a lot to me.
You are welcome, Rhea!!! Thanks for your support with your comment and thumbs up on every video that you learn from, and of course your Sub : ) It means a lot to me to be supported.
Fantastic presentation in which you leave nothing out. Thank you very much
This is incredible. You've broken down an incredible tool and, in the process, made me want to continue watching and learn more and more about Excel and its uses. Thank you for taking the time to produce this video and for sharing your knowledge.
You are welcome, Varsity! Thanks for your support with your comment, thumbs up and your Sub : )
Another Great video Mike! I am taking your data analysis course, and stumbled across this gem. What a great tutorial!
Yes, Scott!!!! This is the fourth video in my Advanced Excel Class at Highline. Glad you like this content and thanks for your support : )
This video was extremely helpful! You explained everything so simplistically. Thank you so much! You rock!!!
You are welcome! Thank you for the kind words! There are many, many more videos just like this at the excelsifun channel. Thanks also for the support with your Thumbs Up, comment and Sub : )
One of the best tutorial i have seen in a long time. Thanks!!
Glad it helps, Shashi!!! Thanks for your support with your comment, Thumbs Up and your Sub : )
I watched many videos about excel, but you are the best among them all. thank you
Amazing professor, videos and accompayning materials to help with learning and retaining knowledge. I had no idea excel was THAT POWERFUL.
Glad to help give you POWER, E L !!! Thanks for your support with your comment and thumbs up on every video that you learn from, and of course your Sub : )
Thank you. You certainly make Excel Fun
A really great introduction to PQ and PP Data Model. Thank you so much for this video.
You are welcome, Jan!!!! Thanks for watching, learning and supporting : )
Been working with Power BI and have old skills from VBA. Learning Power Query now - this tutorial is fabulous, thanks.
You are welcome, Brenda : )
Mr. Mike, Thank u for make me loving Power Query :). Huge contribution for equal opportunities in learning BI tools.
Greetings from El Salvador.
You are welcome! Thanks for your support with your comments and Thumbs Up on each video that you watch : )
I totally agree!
There are lots of great Excel Gurus out there, but you are the best! I have been slogging through other vids for a few weeks now. In 10 min of your vid i already have a better sense of what's going on w/ these new features. thanks for such a great video.
I am glad that I can help with my story telling and helpful examples, Z M!!! Thanks for supporting my efforts with your comments and thumbs ups on each video that you learn from, and of course your Sub : )
Sir you're a blessing to us. I hope one day you have a series of how to be a data scientist for a career shifter. Ive been watching other videos but I was looking for someone who could teach the way how you teach. And I can't find one.. You're incredible sir. Thank you.
I have many videos that you need for Data Science. Look at my excelisfun home page with the Data Analysis classes, Statistics Classes and Analytic Classes.
Reviewing it on the 1st day of 2019. Take my bow sir
Glad you like it, Sazzdur!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Thank you This is one of the best examples I have seen on how to use the new Power Addins. Looking forward to the advance videos.
You are welcome, JG!!! Many, Many more advanced videos, here: ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1 Thanks for your support on each video : )
Thank you for taking a complex task (in my opinion) and making it understandable. That is a gift.
You are welcome, Stepanie! Yes, that is my unique skill: making the complex, less complex. I have this skill because I am not that smart. So in order to understand anything myself, I have to work a very long time, repeating many times, then making up a story for myself that I can understand. Than I tell that story.
Thanks- Valuable tips & tricks-Great Details-
Excellent tutorial as always!.. You are by far the best Excel master on the web. Keep it up Mike!
Thank you for the kind words! Also, thanks for the support with your comment, Thumbs Up and Sub : ) Please support the cause on each vid you watch : )
You are an amazing Tutor! Love your details in a fun way! Thank you so much and stay safe Mike!
You are welcome!
I've always enjoyed your training videos on Excel. You are certainly gifted as an instructor. Thank you for taking the time to share your vast knowledge of Excel!!!! Oh and by the way, I did click the Thumps Up icon and I am a subscriber.
You are welcome, J Sen!!! Thanks for your support : ) Go Team!!!!!!!!!!!!!
I can't wait to learn more about the couple of power tools in Excel - i'm already eyeing your follow-up class on it. But first things first: this advanced class. Looking forward to learning a lot more the coming days and week.
Yes! Following the classes, will give you real power! See you in the comments : )
Simply amazing! Thanks man.
Very powerful tools to manage large amount of Data. I was working already with power queries but I learned more functionalities looking at your videos. The way you explained is also great. Really appreciate that you share your knowledge to everyone.
You are welcome for the share and good explaining : ) Thanks for your support with your comment and thumbs up on every video that you learn from, and of course your Sub !!!!!
Amazing introduction to something we don't know. Great example. Powerful add-in to excel. Great teacher. Thank you very much. Liked, Subscribed, video link sent to my colleagues
Glad it helps, Cristian!!! Thank your very much for your support, it helps : )
Excellent Tutorial !
Thanks for the EXCELlent comment, X caliber!!!
I have watched many tutorials over the last 15 years and I must say this rates in the very top. I hope your other ones are as good.
I am not saying this because I necessarily learned anything new, though I did pick up some cool tid bits I never realized and thanks for that, it is because you method, demeanor and you examples allow you to understand and learn. You are a patient and walk through even the minor steps that others just assume you know. You are a teacher. Well done. Keep up the good work. I will be referring all my colleagues to this video.
Thank you for your kind words about my teaching : )
A wonderful lesson. PowerPivot is awesome. You make it look easy.
I find this video very easy to follow! Thank you for putting this content (with such quality) for free!
You are welcome for the free content, Dre Saviour!!! Thanks for the support with your comment, thumbs up and of course your Sub : )
Thank you for your passionate and awesome tutorial and thanks for the worksheet!
Great Video Michael, I have been following your videos and I really appreciate the clearness and the step by step explanation. Thanks so much for sharing your knowledge.
Love it.
You are welcome, Chuck!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Question regarding one of your workbook named: Busn214SIBWMOE2007Chapter02andChartsfromMGirvin.xls
In this Workbook: Busn214SIBWMOE2007Chapter02andChartsfromMGirvin.xls
The Worksheet name: P144-149
In column A "Style" an "ERR" appears, looks like it is not finding the ='Actual Demand'!_FilterDatabase(H$11,H$13)
_FilterDatabase. ???
I could not figure out.
I would appreciate your feedback.
I've been working with complex VBA to automate templates at my work. Using the data model like you showed me, starting with power query I'm able to build a master template and save tons of time! Thank you for covering the topic with such great specifics!
You are welcome, Robert!!! I remember the VBA for this that we had to use BEFORE Power Query. Power Query changed everything adn makes it easy. Thanks for your support with your comment, thumbs up and your Sub : )
Your videos are great and well put together! Thank you!
You are welcome! Thanks for the kind words : )
Great tips and narration style!
Thank you so much!!!!!! Power Query and Power Pivot are so AWESOME!!! THAT IS AN INCREDIBLE TUTORIAL!!!!
MERCI BEAUCOUP!!!!!
You are so welcome, My Cup Of Tea!!!! Thank you very much for your support with your comment, Thumbs Up and Sub : )
Mike, you are amazing...you have no idea how much you have helped me...your videos are simple, easy to follow and have made me very Excel savvy
I am so glad that the videos help you to become Excel savvy! That is why I post! You can help me to with those Thumbs Ups, comments when you are inspired, a Sub so you know when the latest vid is out and tell all your friends : )
i haven't gotten so much Information from one Video!! I am very very grateful!! THANK YOU!
You are very very welcome, Lara! Thanks for helping to support what I do at RUclips with your comment, thumbs up and of course your Sub : )
Wow!! Fantastic intro with lots of features. Very helpful!!
I am glad that it is helpful for you!!!! Be sure to support the channel with Thumbs Up and comments on each video that you watch and Sub for lots more videos. Although for the next few months I am releasing a Basic Math and Excel series, in spring I have a full series of videos on just Power Query and Power Pivot.
This one helped me a lot. Thx!
Dream come true, Excellent,Amazing free course!,OMG!!! What a tutorial
Glad it helped you, Reema!!! Thank you for your support with your comment, thumbs up and your Sub : )
Great Great thank you for such a wonderful video on power pivot ...
Again thank you from greece.You make me be loved with excel.I wish you have a good life.
Thank you! I am glad that you have found love for Excel - it sure can help us do our job : ) Thanks for your support with your comment, Thumbs Up and Sub : )
Thanks, Mike! You are my Excel Idol!
Glad to help, El Badlis!!! Thanks for your support with your comment and thumbs up on every video that you learn from, and of course your Sub : )
A real "pedagogique tutorial. So enthousiast. I had good fun watching this video. thanks
You are welcome, Gu BO!!!! Thanks you for your support with your comment, thumbs up and Sub : )
Microsoft Excel taken to the next level to clean and handle huge data, presented very clearly in an applicable manner. I have nothing to say about this awesome video but thank you for the gift, Mike!
You are welcome!!!
Thank you for doing so much for sharing the knowledge to many people.
You are welcome for the share to many people!
Great Channel!!!!! Thanks so much for sharing all these!!!!!
You are welcome for all the shares, Eric!!! Please help support all the free resources that I post to the world with a comment and thumbs up on each video that you watch, and of course your Sub : )
wow!!! you touched every single little detail while explaining. The details you covered is awesome.
Glad you like it and it is useful : ) Thanks for the support with your comment, Thumbs Up and Sub : )
Excellent video with step by step instructions. Loved it. subscribed the channel.
Thanks Man, English is my second language and I did not learn Excel from school. I am just trying to learning everything by myself around my small town. I have to say you nailed it with this video. Thanksssssss
You are welcome, khoun!!!
@@excelisfun I am from Cambodia and in Cam the first name is the family name :-P
Thanks for putting up this tutorial~! You're amazing!
(btw liked and subscribed)
You are welcome! And, thank you, Sharon for the support with your comment, Thumbs Up and Sub : )