The title is misleading. I don't think you have to stop using Xlookup. Use both depending on what you need. Xlookup is designed to lookup quickly and cell specific. If I have existing sheet with already many dependents formula, I will use xlookup to get additional data. PowerPivot can't do that because I have to transform both sheets. Some user also use lookup for validation. Example if sheet1 have all the Product name in sheet2 then you use lookup to validate. Very quick and easy. Powerpivot is designed for most likely automation or analyzing large dataset. I know most users don't know or use Power Query, PowerPivot, Data Modeling, Measure, Relationship in excel. Learn and use both.
Hi! And thanks for the comments. 😀 You mentioned some good points about how Xlookup is often used. Yet you'd be surprised what I've seen people do when connecting lists, so the goal of the video was to get them into a different mode of thinking and possibly get familiar with PowerPivot, Power Query, etc. 😀 Be great!
Hello! Beautiful video! I started using Power Pivot about 7 years ago and soon discovered that the best way to get the data inside the Data Model is through Power Query and not directly from Power Pivot GUI (a lot more sources available, huge ETL capabilities, etc). In fact I would say Power Query was built as main ETL for Power Pivot. I know that complexify the beginner's life (some M language over and above the DAX needed in Power Pivot), but the results are simply amazing. And the bonus is that whatever one learns about Power Query and Power Pivot is 100% applicable in Power BI, if ever he/she wants to move into that BI field.
Hi, and thank you for the kind words! 😁 And you are on point with your comment; especially how you develop transferrable knowledge between the three apps. 👍 Be great!
Hi, sorry for the extremely long delay in responding. I never received notification about your comment being posted, and just discovered it while scrolling through comments people posted. I think there are two good sources @chandoo_ and @GoodlyChandeep Both of these gentlemen are very competent and know their stuff. 😁 Thanks for your comment and understanding.
One issue we encounter with Power Pivot is its lack of compatibility with Mac. Consequently, any slicers created using Power Pivot will only display as images for Mac users, which can be frustrating.😕
Hi sir, beautiful video but i have a doubt after creating the relationship between the sheets . Let say here on the above example i want to show both the sheets in a single sheet. Like where ever customer name is present it must show his name, his orders etc.. who to do this without pivot table?
Hi and thank you for the kind words! 😄 And sure, please allow me to share a perspective that might help. There's a chance you could do that and make things work, although you're running the risk of having many- to- many relationships within one sheet, and that can cause problems with reading your data properly. I've always relied on basic database design principles when working with multiple lists and having to connect them; which is- your best bet is to have one topic per table (in this case sheet in excel)- also known as first normal form (1NF) in some database schools of thought. The rationale being you make your data clean and easy to interpret. 😁 Yet in the end, no matter what method you choose, as long as it works- that's all that counts!! Hope this helps! Be great!
@@ProfessorCubs well I am still not convinced that it would be enough to replace xlookup, since ot takes out more memory, but for model references, sure, it could be used
Hi... sometimes people will use VLOOKUP, MATCH/ INDEX or XLOOKUP to combine lists. Power Pivot is an alternative method to use.... plus knowing Power Pivot is a good feather to have in your cap 😁
It is a good video, but there is still a lot more to explore and demonstrate. This is just the tip of the iceberg. It would be nice to create a video based on a real-life example and generate a report based on real-life data
Hi, thanks for the comment. And I agree, Power Pivot can get pretty deep, pretty quick. The goal here was to get people to view thing a little differently. 😀 And I do plan on making some more Power Pivot videos in the future. 😁 Be great!
instead of having to pick one file then the next file, can you pick from a folder so that you only need to pick the folder that contains all the files you need?
@@ProfessorCubs Thank you for responding! run reports for a call center and I created a Dashboard. However, i have to use 2 different reports that only share the dates. I would like to use 1 slicer to apply to my dashboard to control the information from both reports. Hope that makes sense. Sorry.
Hmm, ok... given what you said, and without seeing your data, try right clicking on the slicer and look for 'Report Connections', from there you'll see a list of sources you can connect to. Not sure how your reports are formatted, so you might need to make them into Pivot Tables. Pardon the cheap plug... here is a video that shows you how it works- th-cam.com/video/Zm8QYQwyRYg/w-d-xo.html
The title is misleading. I don't think you have to stop using Xlookup. Use both depending on what you need.
Xlookup is designed to lookup quickly and cell specific. If I have existing sheet with already many dependents formula, I will use xlookup to get additional data. PowerPivot can't do that because I have to transform both sheets. Some user also use lookup for validation. Example if sheet1 have all the Product name in sheet2 then you use lookup to validate. Very quick and easy.
Powerpivot is designed for most likely automation or analyzing large dataset. I know most users don't know or use Power Query, PowerPivot, Data Modeling, Measure, Relationship in excel.
Learn and use both.
Hi! And thanks for the comments. 😀
You mentioned some good points about how Xlookup is often used. Yet you'd be surprised what I've seen people do when connecting lists, so the goal of the video was to get them into a different mode of thinking and possibly get familiar with PowerPivot, Power Query, etc. 😀
Be great!
Yes certainly.
Hello!
Beautiful video!
I started using Power Pivot about 7 years ago and soon discovered that the best way to get the data inside the Data Model is through Power Query and not directly from Power Pivot GUI (a lot more sources available, huge ETL capabilities, etc). In fact I would say Power Query was built as main ETL for Power Pivot.
I know that complexify the beginner's life (some M language over and above the DAX needed in Power Pivot), but the results are simply amazing.
And the bonus is that whatever one learns about Power Query and Power Pivot is 100% applicable in Power BI, if ever he/she wants to move into that BI field.
Hi, and thank you for the kind words! 😁
And you are on point with your comment; especially how you develop transferrable knowledge between the three apps. 👍
Be great!
Do you have any recommendations on videos that helped you the most learn power query?
Hi, sorry for the extremely long delay in responding. I never received notification about your comment being posted, and just discovered it while scrolling through comments people posted.
I think there are two good sources @chandoo_ and @GoodlyChandeep Both of these gentlemen are very competent and know their stuff. 😁
Thanks for your comment and understanding.
One issue we encounter with Power Pivot is its lack of compatibility with Mac. Consequently, any slicers created using Power Pivot will only display as images for Mac users, which can be frustrating.😕
I hear ya, and that really stinks. 🙁
Perhaps one day they'll get it together? 😄
Hi please make full video on power pivot and power query
Only thing is ...i would import data via power query...
That works as well. 😁
Create 1 series of sum product which include everything for sum product n cell referencing cz YT don't have good contain on it..please
Forgot a step. Step 1: don't buy a Mac. Step 1a: reply all - parallels isn't a solution, it's an apology.
Hi sir, beautiful video but i have a doubt after creating the relationship between the sheets . Let say here on the above example i want to show both the sheets in a single sheet. Like where ever customer name is present it must show his name, his orders etc.. who to do this without pivot table?
Hi and thank you for the kind words! 😄 And sure, please allow me to share a perspective that might help.
There's a chance you could do that and make things work, although you're running the risk of having many- to- many relationships within one sheet, and that can cause problems with reading your data properly.
I've always relied on basic database design principles when working with multiple lists and having to connect them; which is- your best bet is to have one topic per table (in this case sheet in excel)- also known as first normal form (1NF) in some database schools of thought. The rationale being you make your data clean and easy to interpret. 😁
Yet in the end, no matter what method you choose, as long as it works- that's all that counts!!
Hope this helps!
Be great!
Count me in..:)
Very well done video, I subscribed and look forward to watching more!
Thank you very much. 😁
Wow... What a great way to teach...Like it😊
Thank you very much!
Be great!!
I loved the video. As I am hooked up on xlookup and wary of power pivot, it completely transform me to become fan of power pivot as well.
Thank you, and glad you were able to get some value from it. 😄
Be great!
@@ProfessorCubs well I am still not convinced that it would be enough to replace xlookup, since ot takes out more memory, but for model references, sure, it could be used
Always had issue with data models especially in cases where a column gets renamed or when an invalid data type appears in a column.
@@Ready_Set_Boom I feel ya... they tend to have a very delicate balance and the slightest changes can make for a bad day. ☹ 😂
@@ProfessorCubsi found that pulling the tables into power query then using that output for pivots make it much more adaptable to changes.
OK, Power Pivot is cool, but how is this relevant to XLOOKUP?
Hi... sometimes people will use VLOOKUP, MATCH/ INDEX or XLOOKUP to combine lists. Power Pivot is an alternative method to use.... plus knowing Power Pivot is a good feather to have in your cap 😁
I've seen so ma vids about this, but I find it too long and complicated but your seem simple and straightforward but also shrot
Thank you very much, glad you found the video useful. 😁
Power pivot? lol. Index Match is the king
no, xkookup, is more adaptable
It is a good video, but there is still a lot more to explore and demonstrate. This is just the tip of the iceberg. It would be nice to create a video based on a real-life example and generate a report based on real-life data
Hi, thanks for the comment. And I agree, Power Pivot can get pretty deep, pretty quick. The goal here was to get people to view thing a little differently. 😀
And I do plan on making some more Power Pivot videos in the future. 😁
Be great!
Love the video, but in some case wouldn't you rather use dynamic arrays as they auto refresh?
Sure, that works too! This is a case of classic excel- many ways to do the same thing. 😀
Is this applicable in online excel?
Hi, and I don't think so. Power Pivot is only available for the desktop versions best I can tell. 😔
A like and a subscription! 😊
Thank you very much! 😄
any exercise file download for self practice
Yes... in the video description area. Look for Sample Data Download 😁
Great work❤
Thank you very much!
Great video, thank you!
Thanks! And glad you liked it!
awesome vide! what do you use for your animation?
Thanks, and currently using D-ID for the animation.
instead of having to pick one file then the next file, can you pick from a folder so that you only need to pick the folder that contains all the files you need?
Hi, off the top of my head, I don't believe you're able since PowerPivot needs to know the individual files you want to use. 😁
@@ProfessorCubs thank you
You're quite welcome 😁
Great job on the video, really easy to follow...
Thank you very much!
Great video. How would you make a relationship using dates?
Hmm... good question. Never had to do that. Can you give a little more information about what you're working with?
@@ProfessorCubs
Thank you for responding!
run reports for a call center and I created a Dashboard. However, i have to use 2 different reports that only share the dates. I would like to use 1 slicer to apply to my dashboard to control the information from both reports. Hope that makes sense. Sorry.
Hmm, ok... given what you said, and without seeing your data, try right clicking on the slicer and look for 'Report Connections', from there you'll see a list of sources you can connect to. Not sure how your reports are formatted, so you might need to make them into Pivot Tables.
Pardon the cheap plug... here is a video that shows you how it works- th-cam.com/video/Zm8QYQwyRYg/w-d-xo.html
@@ProfessorCubs Thank you sir!
You're welcome. 😁
This is awesome!!
Thank you!
Be great!
Thank you
You're welcome! 😁
Helpful 🙂
Glad you were able to get some value from it. 😁