I used to use your forum/ website about 10 years ago to learn a lot about excel. It is amazing how the internet allow you to also come to youtube and do these further explainers and with a little sketch! Thanks for all your work and for being around all this time!
Always loved your website when I was working in IT - great to see you on TH-cam as well - you got me so many brownie-points with your dashboard tutorials. You are in my Hall of Fame of Excel heroes.
@@chandoo_ Well, I did not expect a reply! Just wanted to let you know what a great job you're doing, please keep it up - tutorial/example sites like yours are invaluable to so many people
I appreciate the way you bring across the content as if you are having a conversation, it made what could have been a complex thing to explain quite easy to understand. Thank you!
I have been working so much with Power Bi and M over the last few months that I nearly forgot about the excel basics. DAX in Power Pivot? I did not know about that but it gets a really BIG thumbs up from me. Thanks for showing.
Not only did I hit the Like button, but I also subscribed, such is the power of POWERPIVOTS!!!!!! and your awesome humorous video style. Good job bro! Well done! You are, in fact, awesome!
I use both types all the time. One thing I miss in PP is the grouping feature in the rows from std. pvt tables. Building that into PP requires a ‘more advanced’ knowledge of DAX. BTW: DAX as a formula language is quite cryptic IMO, or as Alberto F. puts is: DAX is simple but it’s not easy. Still, the added power of PP is undeniable.
Thanks for that. Yes, we are not able to "Group" with Power Pivot tables. I show a trick to get grouping in this video 👉 th-cam.com/video/_J23sAGyztY/w-d-xo.html
@@chandoo_ Thanks for that referenced video. :-) The thing is: it is not generic enough: I use/need the grouping for creating histograms, which in std. Pivot tables is easy to do,… and to reconfigure on the spot. (Nowadays you can also use dynamic arrays for this, as well.) And I mean: for more than ‘just’ dates. In order to do this dynamically (variable bin size) you need to use more advanced DAX functions (…) If you have a way of doing that (and a video to show it), I’d be happy to watch it. :-)
I understand. My preferred method is to use Power Query and create "conditional column" with arbitrary bucket boundaries. If you have Power BI, you can natively group values just like we did in Excel.
Hi Chandoo, this is to thank you for the amazing, entertaining, and most importantly, instructive videos about Excel. You've truly succeeded in turning a dull technical subject into a funny and informative one, thanks to your talented humor and your smart and interactive scripts. Be brave and keep up the good work. All the best, my friend 😊👍🏻
Power Pivot and Power Query are very powerful indeed. My complaint is that it's a lot of clicking and submenus. Almost immediately you get into DAX (for Power Pivot) and M language (for Power Query). Alternatively, you can use Python to clean your data, calculate what you need and write an excel file for plotting and sharing. At the end it's a matter of personal preference, but I find Python applicable to a wider set of problems although it does not fully substitute excel (for me at least).
@@shadowlynxv6082 I don't think so and it is not what I meant. I use Python to format the data and write a table/excel file. It is then very easy to import clean data with power query in an excel file I use for visualisation (copy-paste also works for relative small sets). The problem with power query/pivot is that it becomes exponentially more difficult if you have to manipulate data.
Too messy, and one has to learn yet another programming language. Excel, if you understand it, cleans and tranforms data excellently. Power Query and Power Pivot with 'M' and Dax does 99% of all you need in a practical work environment. Python just adds a new level of complexity with little additional benifit.
Excel doesn't support Many to Many relationships. You should use an intermediate table and turn the many ↔ many to Many ← One → Many relationship. Alternatively, you can use Power BI, which supports many to many relationships.
I still use Power Pivot every day even though I've moved on to Power BI. Measure portability and the advantage of DAX measures over calculated fields/calculated items in standard Pivot Tables are key selling points of Modern Excel, aka Power Pivot. And Power Query gives you reusability of data transformations so you spend less time manipulating data & more time analyzing your operations.
- [0:00] 🚀 Power Pivot is a more advanced version of pivot tables in Excel, offering enhanced capabilities for data analysis. - [1:12] 🦸 Power Pivot can handle multiple tables and create relationships between them, enabling more complex analysis without manual data manipulation. - [3:12] 💡 Power Pivot allows for the creation of custom calculations and measures using DAX (Data Analysis Expressions), enhancing data analysis capabilities. - [3:27] 🛠 Practical example demonstrates creating a pivot table with custom calculations like "tickets per employee" using Power Pivot and DAX. - [6:01] 📊 DAX (Data Analysis Expressions) is a specialized language for creating advanced calculations and measures in Power Pivot, offering powerful analytical capabilities.
How did you make to turn the like button in colors??, I literally just pressed the "subscribe" button for that wink action hahaha 😅... You rock on this, and the excel content was also amazing!
Even if you don't SOLELY use Pivot tables, you will need them to display the results of your PowerPivot calculations. Otherwise you have several tables with some results at the bottom. Since the interface and functions are so different than Excel, it will be very difficult for less knowledgeable people to edit them. I only use them if I know it will be only me or people who are familiar with PowerPivot who will be editing them or if my clients want a function that isn't available in a Pivot table.
Awesome content.. very useful for us basic users of excel.. keep it coming dude. I love your humble approach on technical issues with 2 personality acting! I like the superman analogy..😂😂😂
Came for the excellent advice on Power Pivot tables. Left with the bonus of the best joke of the week: "Even Superman looks like a normal dude until he puts the underpants on!" 🤣😂
Hi Chandoo, how can I calculate items in Excel The same number will not be counted twice. My supervisor used to ask me how many items produced per day, how many items produced per Month, and many items produced for each segment. I used to combine day and part number to get items produced per day, item only to get items produced per Month, and item and segment to get items produced for each segment. Can you simplify this? Thank you for answering this question
awesome! I have little query for creating pivot table is their need "data in range" format or can be create in table format, as previously described data should be in range format, please advise
Try to filter and group by dates using power pivot and VBA its frustrating! One issue (show stopper for me) is the user must manually update a calendar(date table) if you have used that feature in power pivot. There is no way I could find to update the date range using VBA. In other words if you provide this to a client and in two years the date table is exhausted by new data, their pivots will be truncated due to the missing dates and you have to rely on them knowing how to fix it and that it even needs to be fixed.
Hi! Thank you for your videos, they are really good. One question, I use Power Pivots to get distinct count, the only problem I see is that you cannot modify your source data with PP, is there a way to do it? Thanks!
This is great information and related to a project I want to do to track pieces at work. This is just what I need to track them with data across different table information that I have. Thank you
I used to use your forum/ website about 10 years ago to learn a lot about excel. It is amazing how the internet allow you to also come to youtube and do these further explainers and with a little sketch! Thanks for all your work and for being around all this time!
Always loved your website when I was working in IT - great to see you on TH-cam as well - you got me so many brownie-points with your dashboard tutorials. You are in my Hall of Fame of Excel heroes.
Wow.. so good to hear that :)
@@chandoo_ Well, I did not expect a reply! Just wanted to let you know what a great job you're doing, please keep it up - tutorial/example sites like yours are invaluable to so many people
Thanks. Learn't something new about Pivot tables. Much appreciated.
You are welcome. Thanks for the super ❤
I appreciate the way you bring across the content as if you are having a conversation, it made what could have been a complex thing to explain quite easy to understand. Thank you!
I have been working so much with Power Bi and M over the last few months that I nearly forgot about the excel basics. DAX in Power Pivot? I did not know about that but it gets a really BIG thumbs up from me. Thanks for showing.
Great to hear!
This is the best explanation I’ve seen, especially around the mapping. Great job.
Not only did I hit the Like button, but I also subscribed, such is the power of POWERPIVOTS!!!!!! and your awesome humorous video style. Good job bro! Well done! You are, in fact, awesome!
I like the sense of humour in you videos. This gives me a boost to see your videos more and more. Keep it up my Excel Master :)
Happy to hear that!
I use both types all the time.
One thing I miss in PP is the grouping feature in the rows from std. pvt tables.
Building that into PP requires a ‘more advanced’ knowledge of DAX.
BTW: DAX as a formula language is quite cryptic IMO, or as Alberto F. puts is: DAX is simple but it’s not easy.
Still, the added power of PP is undeniable.
Thanks for that. Yes, we are not able to "Group" with Power Pivot tables. I show a trick to get grouping in this video 👉 th-cam.com/video/_J23sAGyztY/w-d-xo.html
@@chandoo_ Thanks for that referenced video. :-)
The thing is: it is not generic enough: I use/need the grouping for creating histograms, which in std. Pivot tables is easy to do,… and to reconfigure on the spot. (Nowadays you can also use dynamic arrays for this, as well.)
And I mean: for more than ‘just’ dates.
In order to do this dynamically (variable bin size) you need to use more advanced DAX functions (…)
If you have a way of doing that (and a video to show it), I’d be happy to watch it. :-)
I understand. My preferred method is to use Power Query and create "conditional column" with arbitrary bucket boundaries. If you have Power BI, you can natively group values just like we did in Excel.
Thanks heaps, your videos are "Really"&"GREAT"!
Thank you for the love and super :)
Hi Chandoo, this is to thank you for the amazing, entertaining, and most importantly, instructive videos about Excel. You've truly succeeded in turning a dull technical subject into a funny and informative one, thanks to your talented humor and your smart and interactive scripts. Be brave and keep up the good work. All the best, my friend 😊👍🏻
Basically, it acts as an SQL query with join applied 😁
And this data should have been in MSAccess in the first place...
Why do we require 20+ years to have related tables
Power Pivot and Power Query are very powerful indeed. My complaint is that it's a lot of clicking and submenus. Almost immediately you get into DAX (for Power Pivot) and M language (for Power Query). Alternatively, you can use Python to clean your data, calculate what you need and write an excel file for plotting and sharing. At the end it's a matter of personal preference, but I find Python applicable to a wider set of problems although it does not fully substitute excel (for me at least).
Wait. Can you stick Python into Power Pivot. And if so, 1:06 is it better than Dax or M?
@@shadowlynxv6082 I don't think so and it is not what I meant. I use Python to format the data and write a table/excel file. It is then very easy to import clean data with power query in an excel file I use for visualisation (copy-paste also works for relative small sets). The problem with power query/pivot is that it becomes exponentially more difficult if you have to manipulate data.
@@FranFerioli oh. I see.
Too messy, and one has to learn yet another programming language. Excel, if you understand it, cleans and tranforms data excellently. Power Query and Power Pivot with 'M' and Dax does 99% of all you need in a practical work environment. Python just adds a new level of complexity with little additional benifit.
Do you have any tips for dealing with many-to-many relationships in tables?
not possible
Excel doesn't support Many to Many relationships. You should use an intermediate table and turn the many ↔ many to Many ← One → Many relationship.
Alternatively, you can use Power BI, which supports many to many relationships.
Chandoo, awesome content, as usual. I like the way you have presented it and it was nice meeting Chan2! Good team work.👏
Thank you!
You're welcome! Thanks for the Super 😍
So you're still using pivot tables 😂
Great video Chandoo. I am also using Power Pivot a lot more, though I still use regular pivot tables for simple analysis.
Glad to hear that Chris. :)
Was not expecting « Even superman looks like a normal dude until he puts some underpants on top » as an argument for a new excel feature 😂
I still use Power Pivot every day even though I've moved on to Power BI. Measure portability and the advantage of DAX measures over calculated fields/calculated items in standard Pivot Tables are key selling points of Modern Excel, aka Power Pivot. And Power Query gives you reusability of data transformations so you spend less time manipulating data & more time analyzing your operations.
AWESOME points... With "Analyze in Excel" you can build your models in Power BI and still have them available in Excel for reporting.
Why did you move to Power BI?
1:11
Even Superman looks like a normal dude until he puts on the underpants on top. Amazing quote ! 🤣 👍
distinct count has been so useful -- makes things so much easier
- [0:00] 🚀 Power Pivot is a more advanced version of pivot tables in Excel, offering enhanced capabilities for data analysis.
- [1:12] 🦸 Power Pivot can handle multiple tables and create relationships between them, enabling more complex analysis without manual data manipulation.
- [3:12] 💡 Power Pivot allows for the creation of custom calculations and measures using DAX (Data Analysis Expressions), enhancing data analysis capabilities.
- [3:27] 🛠 Practical example demonstrates creating a pivot table with custom calculations like "tickets per employee" using Power Pivot and DAX.
- [6:01] 📊 DAX (Data Analysis Expressions) is a specialized language for creating advanced calculations and measures in Power Pivot, offering powerful analytical capabilities.
Had to click the like button for that, toing toing sound and expression... 😂😂😂
You are the only guy made learn excel made like watching cartoons.
You are the funniest excel instructor!
Dooh! I really enjoyed clicking on the "Like" button. (Insert Chandoo's eyebrow movements!). Great job on this content. Cheers!😎
:) Besides providing very useful information,I have a lot of fun while watching your videos.
That is awesome!
Your teaching skill is on another level.
This explained a lot. Thank you!
The twins ave a mesmerizing effect, they made me hit the like button!
They thank you :)
Clicking the like button just for that innovative dialog 😁
Thanks Chandoo. I’ve always checked the add to data table box but wondered where or what it did
Chandoo, I hit the like button You are an Excel Einstein! :)
First video I watch from your channel. I'm gonna smash the subscribe button😂😂
Didn't know Excel has this kind of power. Your content is 💯.
Love it. Thank you, Chandoo. You the best!!
Respect to you Chandoo !! ❤❤ following you since 2007!!
Wow.. Thank you so much for being with all these years 😍
"Even Superman looks like a normal dude until he puts the underpants on top". You are wise beyond your years 🤣🤣🤣
This is a great video. Love the humor too.
Hi Chandoo, very helpful…good to see your creative and funny side together!
Love that you keep it original. Great video. Would help a lot of finance folks.
What a life saver, thanks Chandoo !
Thanks. Is it counting distinct names?
Great video, and great sense of humor 👍👌💯
Hi Chandoo, is there any reason why am not able to view other table under All in the pivot table fields section?
Never seen one of your videos, this was *very* entertaining, especially for an Excel tutorial haha, Great job!
Glad you liked it!
Thanks Chandoo bro! Always learning something valuable from your videos.
👍🏻 Very interesting hint. Funny made clip! 😊
When Chandoo said hit the like button. I literally hit the like button :D
😎😎😎
When you put the underpants on top 🤣
Awesome vid dude!
wonderfull man. Loved your video, you make everything looks easy!!! Congrats!!!
As usual Chandoo is back with wonderful video. Really missing "I saw my Boss" videos 😀...
I love his videos! He is a lot of fun and very informative! 🙂
Amazing Chandoo and thank you very much for all your valuable information
Woow...great short and well explained video! thank you
Thank you! You just helped me solve a huge challenge I had.
Nice explanation with having fun
Like it Chandoo 👍
Or make the view with joins outside of the spreadsheet before even bringing into the spreadsheet in the first place?
Nicely presented sir, and explained in simple language..
How did you make to turn the like button in colors??, I literally just pressed the "subscribe" button for that wink action hahaha 😅... You rock on this, and the excel content was also amazing!
Wow! great learning from you!
Have started power pivots and results are amazing
Great to hear!
First time someone managed to stuff underpants into power pivot tables ... super!
Even if you don't SOLELY use Pivot tables, you will need them to display the results of your PowerPivot calculations. Otherwise you have several tables with some results at the bottom. Since the interface and functions are so different than Excel, it will be very difficult for less knowledgeable people to edit them. I only use them if I know it will be only me or people who are familiar with PowerPivot who will be editing them or if my clients want a function that isn't available in a Pivot table.
This is what I'm looking for before. Thank you for sharing this!
Your videos are always amazing Chandoo. Thanks for sharing!
Glad you like them!
I have been following our friend @chandoo since 2019! Keep it going as your videos are very helpful my friend!
Always awesome Chandoo.
Thanks for awesome video always.
That was pretty cool. Liked and now a subscriber
It is so nice to talk to a smart person...
I know I discovered gold the first day I subscribed to your channel!!!
Funny and educational. Good job.
I hit the like button at the superman joke, thanks Chandoo!
It's a amazing tool and it's an amazing explanation ❤❤😊😊
Awesome content.. very useful for us basic users of excel.. keep it coming dude. I love your humble approach on technical issues with 2 personality acting! I like the superman analogy..😂😂😂
Thank you :)
That effects around like button...was awesome...
Came for the excellent advice on Power Pivot tables. Left with the bonus of the best joke of the week: "Even Superman looks like a normal dude until he puts the underpants on!" 🤣😂
Very Nice. Interesting informative and yet fun to watch video.
Great content man..
Wow this channel is improving. Now he adding extras in his videos. Hope it wasn't too expensive.
Is there a way to add your pivot tables to the data model after they are created?
Really good. Love the presentation style as well. Thank you
Great video production and lesson
Glad you liked it!
Thanks for putting this together.
Really great explanation ♥️
Hi Chandoo, how can I calculate items in Excel The same number will not be counted twice. My supervisor used to ask me how many items produced per day, how many items produced per Month, and many items produced for each segment. I used to combine day and part number to get items produced per day, item only to get items produced per Month, and item and segment to get items produced for each segment. Can you simplify this? Thank you for answering this question
Brilliant! Love the video.
Thank you for sharing knowledge.
awesome! I have little query for creating pivot table is their need "data in range" format or can be create in table format, as previously described data should be in range format, please advise
Excellent . I always learn something from you. 👏🏻
The power of the underpants proven! Thank you Chandoo!
Chandoo you are super hilarious.
Amazing information Chandoo. Helping me a lot with my daily activities.
Thank you for the information!!!❤
Try to filter and group by dates using power pivot and VBA its frustrating!
One issue (show stopper for me) is the user must manually update a calendar(date table) if you have used that feature in power pivot. There is no way I could find to update the date range using VBA. In other words if you provide this to a client and in two years the date table is exhausted by new data, their pivots will be truncated due to the missing dates and you have to rely on them knowing how to fix it and that it even needs to be fixed.
Hi! Thank you for your videos, they are really good. One question, I use Power Pivots to get distinct count, the only problem I see is that you cannot modify your source data with PP, is there a way to do it? Thanks!
Great tips to increase the productivity
Brilliant, thank you for this, helps a lot.
wow you earned my subscribe. Very very useful, I'll be using this for work
Thanks for helping us to increase our knowlege
This is great information and related to a project I want to do to track pieces at work. This is just what I need to track them with data across different table information that I have. Thank you