MSPTDA 13: Power Pivot Introduction #1: Relationships rather than VLOOKUP for PivotTable Report
Вставка
- Опубліковано 4 жов 2024
- Download Excel START File: people.highlin...
Download Excel FINISHED File: people.highlin...
Download Example of Large File Size using VLOOKUP: people.highlin...
Download pdf Notes: people.highlin...
Assigned Homework:
Download Excel file and complete the homework:
people.highlin...
Examples of Finished Homework:
people.highlin...
This video introduces Excel Power Pivot and shows how to use the Relationship feature rather than VLOOKUP Function to reduce file size and allow user to pull fields for a PivotTable Report from Multiple Tables.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction
2. (01:43) Look at VLOOKUP solution
3. (02:49) Introduction to What Is Power Pivot? Discuss what a Data Model is.
4. (05:46) Relationship feature
5. (09:16) Create PivotTable Report
6. (11:45) First Look at Implicit Measures
7. (13:02) Enable the Power Pivot Ribbon Tab
8. (13:37) First Look at Power Pivot Window and Data Model
9. (20:30) Summary
If this is the 12 century (when knights were a popular system to be loyal to their lords), I'd like to call you my EXCEL LORD! Your lessons are AMAZING!!! THANK YOU!
Yes!!!!!! 12 century fun : ) : ) : )
Relationships are great! Makes it all so simple to setup. Thank you Mike for another great video. Looking forward to the next ones in this series.
Thanks, Teammate Leila!!!! Thanks for the support : ) Yes, there is lots more Power Pivot and DAX fun to come !!!
Enthusiasm, No sh#t talk, Perfect Structure, Lots of Learning, Always New Concept, An Exemplar. Thanks Mike❤❤❤
Mike's Excel knowledge has one to many relationships with other Excel users in the World!! Keep going...
You GIVE and you are LIMITLESS, I have learn so much, and I am forever grateful to YOU, THANKS
You are welcome, Rolandolau!!!!! You can forever support with those Thumbs Up and comments - and a LOT more fun with Power Pivot to come!!!
Literally the only person I watch for excel videos!! Always appreciate the enthusiasm in your videos!
You are welcome, 66koolkb!!!! Thanks for watching and thanks for the support of excelisfun, 66 - cool - kb!!!!
ComputerGaga has some good Excel stuff as well.
I have nothing to say but u are always the best and u know it thanks a lot for helping me change my life and save my company tons of times
You are welcome for the help, change and saving time, Ismail!!!! Thank you for your support : )
You are a HUGE blessing to us all. May God bless you with health and love.
Glad to help, Henry!!!! Thanks for the support : )
You are my favorite channel when it comes to teaching excel. I learn most from this channel.
6:50 , Wow you explained it , not expecting that , thank you so much .
You are welcome!!!
Thanks Mike. I was about to use some VLOOKUP formulas in a large spreadsheet but your video reminded me of the better way to do it. Your videos are always helpful!
I am so glad to hear that, Rob!!!! Also, when you see the next video, you will see that when we import external data for PivotTable Reports, we basically never want to keep the tables in Excel, but rather in the data model. Glad the video helps and thanks for your support : )
Thank you Mike. I thought I already knew the basics on powerpivot and you prove me wrong yet again. You are an invaluable resource.
Lots more basics to come, jidduv!!!! Thank you for the support with your comment, Thumbs Up and Sub : )
I love when you keep your videos simple and easy to understand!
This channel is so incredibly valuable to so many people. Thank you for spreading knowledge. My fav Excel video person. As action packed as it gets for these things.
You are welcome : )
Relationship makes Vlookup easy ❤️..many thanks to you 🙏
Yes!!!!
Stumbled across this series and looking to find the time to view them all in order. A fantastic resource that will save an enormous amount of time and money in future. Appreciated!
Thank you Mike for explaining relationships like a cakewalk!!
You are welcome for the cakewalk, Mohit : )
Your channel is one of the most valuable ones on UA-cam. Much respect and thanks for sharing your knowledge! Kudos to you, sir.
You are welcome, ninja-poof! Thanks for the support with your comment, Thumbs Up and Sub : )
Awesome video as always! Every video I learn something amazing from you.
Glad that that is TRUE, Pravin!!! Thanks for your support on each video with those comments, thumbs ups and of course your Sub : )
Hi Mike.. another great video. Excel can do so many awesome things, it's hard to pick an emphasis or focus. Your excellent videos and playlists along with your clear and concise instructions, keep me on track, learning something new every day. Thanks and Thumbs up!
You are welcome! Excel is amazing. Funny thing is, I posted this video 25 days ago, and my intent was to continue this series, right away, and then Excel released the New Excel Calculation Engine with the New Dynamic, Spilling Arrays, and I had to put this series on vacation for about a month while I made "breaking news" videos about that amazing new feature. I will be back posting videos for this in about a week! Thanks for your consistent, support, Wayne!!
Good to hear and looking forward to it. I find myself bouncing between the new video posts, your featured series and the Highline College playlists. I wish I could take a year off and do nothing but your videos.. haha!! Short of that, I just try to absorb as much as possible each day and then apply it to my work for immediate benefit. Can't wait for those dynamic array functions to land.. and I was just getting the hang of CTRL-SHIFT-ENTER.. haha!! Thanks again for all the great resources.
I have learnt significantly from these videos
Thank you for the introductory video - even just using the relationships is a leap forward from Standard Excel.
I'm glad they put PowerPivot back in Office365 Personal!
I am too! Microsoft was crazy to ever NOT include it in all versions!!!!! Thanks for your support, don town, with your comment, Sub and Thumbs Up : )
Brilliant. I wish I could be as good as you are in Excel, Mike. Many many thanks for your valuable efforts. I highly admire your unlimited knowledge in this software. All the best !!! You are EXCELLENT.
You are welcome, Salim!!!!! Thank you very much for your support on each video : )
At 01:47 that graphical representation of One to Many relationship is outstanding!
Funny that its not portrayed in other courses.
Mike, I am happy I have come back : )
Ok, enlightening moment-os at:
- 03:00 (explaing Power Pivot)
- 05:09 (key benefit ! )
- 06:36 (nice call: the secret backdoor way to relationships ! )
- 06:54 (explaining on joining tables)
- 08:14 (great spot on! on this detail)
- 10:00 (a big clarification ! thanks!)
- 10:56 (another great spot on! Mike)
- 11:56 (a cool feature to know ! )
- 12:27 (nice call on implicit measures)
Yes, many details and visuals are not in other videos or books. I try to tell a complete and fun and visually easy story : )
I am always happy when you stop by, Teammate!!!
@@spilledgraphics , yes lots of cool details. Glad you like it all : ) : )
Your videos are the best way for excel learning! Thank you!
Glad they help, kostas! Thank you for your support : )
Thank you, Mike. An excellent addition to a strong series. (Yes, I know, I always say that. But what to do, when you just keep the niveau , Mike.) Power Pivot & Query are solid advanced material and set your mind to working with numbers and data.
You can keep saying it - it helps me to keep going and keep the excelisfun channel getting to new niveaus!!!! 10 years, over 3,000 video, so we just got to keep going - plus this Power Query, Power Pivot and Power BI Desktop are all just so cool : ) Thank you for your support, M. SZ.!!!!
Thanks for another great video, it's been a great series. Thank you Mike!
You are welcome, Luciano!!!! So much more fun to come with Power Pivot!!!!! Thanks for your support : )
Great new video for me and new topic to study on a deeper level
Glad it is helpful, Orca : )
I am learning so much from you. Thank you for making this so enjoyable to learn.
You are welcome for the enjoyable learning, ytawn!! Thank you for your support on each video : )
Excelente!! who shares what he knows reveals his true essence..muchas gracias por compartir!!
You are welcome, fabry!!! Thanks for your kind words and support : )
Thanks Mike. Power Pivot videos, here we go :) :)
Yes, John!!!!! I can't wait for the DAX too, John Borg!!!!! The video making is slow right now because I have had family obligations... And I am just as excited as you about the up and coming Power Pivot and DAX videos : )
Great as always. Thanks Mike
You are welcome, K B!!!! Thanks for for your support : )
Thank you very much for everything you doing for us!!!
You are welcome, Oleg! Thank you very much for support on each vid you watch with thiose Thumbs Up and comments : )
Power Pivot....I put that s^!t on everything... Thanks Mike
Thank you so much! I've been learning a lot from your videos!
You are welcome, Glad you are learning a lot, Hua Lu!!!
Many many thanks!!
😄
You are welcome, Ms Beau!!!
Well done, Mike !!! Thanks:-)))
Thanks O Poetic Teammate : )
EXCELlent video Mike. Thanks for sharing
You are welcome for the share, Syed : )
Thank you brother!
You are welcome, Tanzeem!!!!
AN AWESOME THUMB AND MANY LIKES FOR YOUR WONDERFUL VIDEOS
Thanks for your support, Ahmed!!!! Glad it all is awesome : )
That topic is later in this class. But here it is: ua-cam.com/video/wcwV_RKOS5U/v-deo.html
If you go to the full class, the Many to Many section has a few videos: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
Thanks for the video Mike!
You are welcome, Joshua!!!!
Info is really helpful.
Glad it is helpful for you! Thanks for your support : )
As usual, wowwwww!!!
Glad you like it!!!!
Great video as always!
: )
As always thanks for the great video
You are welcome, Purplecrystal Sh!!! Thank you very much for your support with your comments, Thumbs Ups and Sub : )
Thanks mike girvin excelbrain :-)
You are welcome, Mohamed!!! Thank you for your consistent support : )
Excellent video.
Thanks for relationship-power-pivot fun :)
You are welcome for the relationship fun, Teammate!
Thank you Mike :)
Great: now you are getting into the Power Pivot Relationships and and DAX and Data Model and Columnar Database videos = so much fun!
Great as usual :). Thanks Mike
You are welcome, nimrodzik1!!!! Thank you for the support : )
Thank you. :)
Thanks a lot for your exellent sharing!
You are welcome for the EXCELlent sharing!!! Thanks for your support with your comments and Thumbs Ups and Sub : )
Thanks Mike
You are welcome, Dave! More Power Pivot to come : )
Nice beginning to PP, Mike. We just *know* this is gonna be great...
On a side note: are you aware of the brand spanking new Excel functions called “Dynamic Array” functions that use the ‘spill’ feature?
That really looks awesome!...
Glad it is nice - it has been a few weeks since I made a video because of family obligations, and it may be slow for a while, but I am excited about making Power Pivot and DAX videos! Yes, I am aware of the new Dynamic Array formulas, it just has been hard to get any work done. I can't wait to make videos on that topic too : )
ExcelIsFun don’t worry, Mike, family matters come first, video making comes after that.
The quality is so good - we’ll be waiting patiently. Take care!
Thanks, Teammate!!!!
Another great video!!
Glad it is great for you, Emmanuel!!! Thank you for the support : )
Thank you sir
You are welcome, Most Awesome Supporter, M. Alomery : )
Awesome Mike !!!!!!
Glad it was awesome for you, Chris! Thanks for your support : )
Quick Refresh :-)
Glad it was fresh for you, Mohamed!!!! Thanks for your consistent support!!!
Wonderful vedio..
Glad it is wonderful for you, amit!!!!! Thank you for your support : )
Thanks
You are welcome, Henry : ) Thank you for your support !!!
Great information, what if you have many to many relationships though. I have tried to use some joiner tables, but I cannot seem to be able to pull in fields from the main tables to perform sums on. I assumed I could use calculated columns to them use these new total fields in the pivot table but it all sort of just falls apart and errors out with each field. Do you have something that specifically refers to calculating values in a many to many relationship?
very good
Glad it is good for you, Steven! Thanks for your support : )
Hi Mike Girvin, this is amazing. How can I take your course? I need way more hand-holding. Do you teach at a college? In person, online? Do you have very simple books or guides? Need learn fast. Do you do private tutoring? I am in So Cal. Thank you in advance, Brendan
I am finally getting around to investigating Power Pivot. Great video as always. I will watch the full series. The question in my head right now is why would I use Power Pivot instead of using PowerQuery? ok, if I had million(s) of rows then a normal PT wouldn't work. But I also don't see how power pivot can have the equivalent of multiple criteria vlookup. I can do that in PQ. Sure, some of the aggregations in PowerPivot are better (e.g., count distinct) than a normal pivot, but again, I can get a distinct list in PQ. I want to use this tool, I just don't have the use case in my head right now.
There are two main reasons to use Power Pivot: 1) if you have millions of rows, even 100s of millions of rows, no other tool can deal with it as efficiently as Power Pivot or Power BI Desktop and the Data Model. The main reason in the engine behind the Data Model (talked about in the next video a lot) deals with all the stored unique lists and can filter the huge Fact Tables table down to much smaller sizes before it ever makes a calculation. Excel Worksheet Formulas and Power Query do not have that ability. They can not make the table smaller before making a calculation, each tool must iterate over all the rows and can therefore be significantly, to the point of slowing everything down to a painful level, slower. 2) There are some calculations that Standard PivotTables have a hard time with, like daily average sales or unique counts, that DAX formulas can make more easily. Keep watching, and you can see this. The other thing is, if you never really have Big Data and calculations in the Worksheet and a PivotTable are sufficient, no need to jump over to DAX. One other note (also as you will see later in this class) Power BI can make some Shareable Interactive Dashboards much, much more efficiently than Excel.
ExcelIsFun thank you very much for taking the time to answer. I will watch the rest of the series. The very first time I went to use the data model I needed to join two tables based upon TWO fields and I could not see how to do that so I gave up. I want this tool in my arsenal though!
@@JonathanExcels You would have to create a calculated column and join the two keys. Then make the relationship.
@@excelisfun hahaha this is the charm of reading comments! I just found what I' ve been looking for for the past 1 week! Thank you!
@@JonathanExcels I really want to thank you for asking the question too!
👌🏽👌🏽👌🏽
: ) : ) : )
Awesomee..... So we r soon hitting the DAX....love to follow every bit of it..
Lately gil raviv book on power query is out... Have u seen that mike....
I have not seen the book. But I just went to amazon after you mentioned it, and tried to order, but it says it is not out yet... I can't wait for the DAX too, RRR!!!!! The video making is slow right now because I have had family obligations... So I am just as excited as you about the up and coming DAX videos : )
You said you had a slow iterating DAX formula is a comment a few days ago and I replied and ask what sort of DAX Formula you had, but I never heard back from you. I am curious what your formula is ?
One can use power pivot for the relationships, and also can use power query merge to do the same. When to use which and what are the pros and cons?
When one flat table is okay, like in small table in Excel Worksheet to make a standard PivotTable, then Merge can be good. But when you are creating Data Models for small and big data and want to leverage the amazing power of the Data Model and DAX Formula innate filtering capabilities, then you 100% do not want Power Query Merge, you want Relationships. I am not sure how far you are through the MSPTDA class, but in later videos you will learn about the filtering of tables that happens in the Data Model with DAX formula, which exclusively relies of multiple tables and Relationships. This capability provides two benefits: it can more easily makes some calculations, like a daily average, but more importantly it works very fast on Big Data. Having the lookup or Dimension Table in the data model and using a column from that table, rather than a Merged column can have unparalleled advantages for Big Data and some specific calculations. However, later in the class in the Data Modeling section, we do see that Power Query Merge can be used in pre-steps of the Data Modeling process to transform complex data into the right form before we load the data to the Data Model and Build Relationships. So as always, the answer sort of depends : )
Thanks a lot for all your help Mike. So far I could use Version 2010 to do most of the HW. I don't see Relationships in the Data tools, finding it hard to do this HW in 2010. Any thoughts? Once again thank you for all your help and support.
Your version does not have many of the necessary tools. Sorry. THE only version to have moving forward in history is; Office 365. Get it as soon as possible because the extra $ you spend are well worth it : )
Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
@@excelisfun Of Course, I will continue to do that. You are doing an incredible service to the global community, Mike. Thank you so much.
@@TheTekelp Together we are a Team: I make and post, you watch and support : ) Go Team!!!!!!
Nice
Glad you like it, hakim!!!! Thanks for your support : )
Dear sir, Can I get the date wise stock report requirements
Such as opening,purchase, sale and then closing
Next day to carry forward the closing as opening
If new purchases match with opening then unique them and new with vba/formulated base.
Please revert as early
Hello ,
As far as I understand , this feature replaces vlookup only when creating a pivot table.
Can we use it to add a column to a given table ? 😊
Thanks
Yes, if you wanted to pull all the one side items to the many side in a Calculated Column, in the Fact Table (many side) you would use the RELATED function.
I have Office 365 (Up-to-date, afaik) and still have different icons for Data Model Pivot Tables...
Yes, it is just crazy how many different version there are these days... I guess we just have to get used to the differences : )
nice
Glad it is nice for you, Abdul!!!
Is it better to make relationships with power query or with power pivot data model, if you have big data?
Relationships can only be created in the Data Model. If you are in Power Query the Merge feature could be used, but then you would have a single flat table.
How do you explain that with this feature to file size drops ?
Unfortunately I have problems with downalding excel files, is there any problem with the website the files on which te files are? Does anyone have the same problem?
Thanks for sharing. Can i use your data for my Video, please?
You are welcome! Thanks for your support with your comment, Thumbs Up and Sub : )
Yes, you can use the data, but be sure to say that you got it from the excelisfun channel.
Also, I just Subbed to your channel : )
@@excelisfun yes, thank you
Hello Mike, the link for the file isn't going through.
The links seem to be working. Can you please check again and report back to me what is happening? Also, which file is it that is not working? I tried them all and they seem to be working.
top
Glad you like it, Sevag!!!! Thanks for your support : )
the Thanks to you Mike All this information in one place in: "ExcelIsFun"