That is just about the best thing I heard today. Thanks David (and I'll see you on the next one 😀). Oh, and you definitely should check out Power Pivot.
Gasper.. that was a great refresher on PowerPivot. I use PowerPivot a lot in my office and like you said in the video one of the great features of PP is the asymmetric reporting capabilities thanks to the SSAS tabular running behind the scenes inside excel. I will go and read your article. Happy new year! Thanks for posting
Hi Gasper. Good morning. Look forward to next video. Also, i forgot to tell you that i liked the way you used the HASOVEVALUE function to obtain the average at the grand total level of the pivot table . Very clever.
Awesome Gasper! Was not aware of managing sets. Power Pivot really opens up new possibilities. Thanks for demonstrating. Looking forward to more! Thumbs up!! PS - Thanks for providing the sample data. Always fun and instructive to follow along :))
Wow! thank you, what a very informative demo. I stopped using excel a long time ago because of how restrictive it was and opted for a tool called Monarch (which I love) but now I’m heading back to excel using all the powder features and thinking Monarch and I could be breaking up real soon. 😅
Only came across this today. Attended one of your workshop in Amsterdam in 2019. I see the link to the exercise files does not exist anymore. Where can I find the LARGE file. Want to use when training. Thanks. Best wishes from South AFrica.
The OLAP tool "Convert to formulas" worked differently for me. It converted the power pivot to a range. Pls help. Been trying to multiply column Y by column Z in my power pivot table and have the result column be part of my power pivot, and I've been unable to do so. Super Frustrating.
Need one suggestion. In the raw data/source datasheet, I have one column for Birthday information in Date number format. Furthermore, Some data are missing and some cells are mentioned with non-number values (i.e "N/A", "Not update" etc.). Now the problem is when I add this Table into power pivot, Dates are stored as text. That's why I could not add any function. Can you please how to solve this?
My suggestion would be to use Power Query to get the data into Power Pivot and during the transformation you can deal with n/a values and other problems and definitely defining the column as Date data type. Than everything will work.
0:43 And I can name 2 of those 3 differences: 1) You can create a PivotTable from multiple worksheets with a PowerPivot. With a traditional PivotTable, you can only use data from the current worksheet. 2) With a PowerPivot, you get the option to calculate a Distinct Count. With a traditional PivotTable, you do not have that option.
Wondering, what is the best way to combine 2 different datasets that has slightly different fields? I am trying to make a file that has actual sales for the closed months, but future months need to added from a forecast table to make my outlook.
You don't need to combine the two. There is a case to be made that you do not want to have a single column of values and one of the dates, as this does not allow you to differentiate between AC and FC. If you connect both tables to all needed dimension tables, it will work like a charm :)
piqued my interests. i have huge report that uses power query into a pivot table but its size is 85mg and takes 15 minutes to refresh the data from the server. i'm going to translate all to power pivot.
Hi there, this is amazing. I do have a question. i have already loaded 9000 records into the power pivot and built a dashboard based on it, it is a monthly report. but now problem the is, each month i have new sales data. in that case, how to just load or add the data to the table that 's already in the power pivot??? cause i tried many ways, it would just add the data to a different tab, like a new table, but that is not how i want it. please help. thanks in advanced.
@@ExcelOlympics oh no... really, what i did was adding the data in excel to data model directly, did not use the power query. and , i deleted the orginal data in excel. so bascalliy now, the power pivot has the data, and I build the report based on the data on the power pivot. oh my god, so, does that mean there is no way i can append the new data to existing data model without using power query??
Well the "I deleted the original data" could be a problem. But you do not need to build the whole thing again. You bring in the table again, this time with Power Query. You then connect the table to the model and edit all the measures so that they point to the new table. And in theory everything should work, even the Pivot Tables... This would allow you to then delete the original table.
![BabyChiefDoit - Too Slow (feat. STAR BANDZ) [Official Music Video]](http://i.ytimg.com/vi/zayaxws7RPg/mqdefault.jpg)
Master black belt excel👍 Thank you Gašper🙂
Thank you for those kind words Davor. Means a lot!
Thanks Gasper, I always perk up when I see one of your new videos. I have been aware of power pivot but never really dug in, now I will. 😎
That is just about the best thing I heard today. Thanks David (and I'll see you on the next one 😀).
Oh, and you definitely should check out Power Pivot.
Gasper.. that was a great refresher on PowerPivot. I use PowerPivot a lot in my office and like you said in the video one of the great features of PP is the asymmetric reporting capabilities thanks to the SSAS tabular running behind the scenes inside excel. I will go and read your article. Happy new year! Thanks for posting
A great 2022 to you too jazzista! See you on the next one 😀
Hi Gasper. Good morning. Look forward to next video. Also, i forgot to tell you that i liked the way you used the HASOVEVALUE function to obtain the average at the grand total level of the pivot table . Very clever.
Thanks Jazzista. It's a technique I use all the time 😀.
Extremely useful tutorial. Thanks!
Thank you for those king words Alex.
Awesome Gasper! Was not aware of managing sets. Power Pivot really opens up new possibilities. Thanks for demonstrating. Looking forward to more! Thumbs up!! PS - Thanks for providing the sample data. Always fun and instructive to follow along :))
Thanks Wayne. Sample data should be a rule from now onwards (unless AutoSave overwrites it again 😀).
@@ExcelOlympics Awesome! Very helpful and much appreciated :))
Sorry, my mistake. I see if I click on the link it starts downloading automatically.
Wow! thank you, what a very informative demo. I stopped using excel a long time ago because of how restrictive it was and opted for a tool called Monarch (which I love) but now I’m heading back to excel using all the powder features and thinking Monarch and I could be breaking up real soon. 😅
Glad to bring you back to the Excel camp Tracy 😀 Hope the break-up goes smoothly 🤣
Thank you, with your videos,I always learn something new and interesting...keep on rocking, Gasper!
Thank you for those kind words Lena. It warms my heart to hear them.
Great. Thanks a lot! Are there new things to come, Gasper?
Thank you- this was great!
Only came across this today. Attended one of your workshop in Amsterdam in 2019. I see the link to the exercise files does not exist anymore. Where can I find the LARGE file. Want to use when training. Thanks. Best wishes from South AFrica.
Great tutorial!
Thank you for those kind words Aliz.
17:00 best description of the data table
Thank you for those kind words Doug.
Nice video Gasper 😊
Thanks Martin.
The OLAP tool "Convert to formulas" worked differently for me. It converted the power pivot to a range. Pls help. Been trying to multiply column Y by column Z in my power pivot table and have the result column be part of my power pivot, and I've been unable to do so. Super Frustrating.
Nice , thanks
Great job from France, Can you share the data ? I'm doncic fan 👍👍👍
Need one suggestion. In the raw data/source datasheet, I have one column for Birthday information in Date number format. Furthermore, Some data are missing and some cells are mentioned with non-number values (i.e "N/A", "Not update" etc.). Now the problem is when I add this Table into power pivot, Dates are stored as text. That's why I could not add any function. Can you please how to solve this?
My suggestion would be to use Power Query to get the data into Power Pivot and during the transformation you can deal with n/a values and other problems and definitely defining the column as Date data type. Than everything will work.
0:43 And I can name 2 of those 3 differences:
1) You can create a PivotTable from multiple worksheets with a PowerPivot. With a traditional PivotTable, you can only use data from the current worksheet.
2) With a PowerPivot, you get the option to calculate a Distinct Count. With a traditional PivotTable, you do not have that option.
Wondering, what is the best way to combine 2 different datasets that has slightly different fields? I am trying to make a file that has actual sales for the closed months, but future months need to added from a forecast table to make my outlook.
You don't need to combine the two. There is a case to be made that you do not want to have a single column of values and one of the dates, as this does not allow you to differentiate between AC and FC. If you connect both tables to all needed dimension tables, it will work like a charm :)
piqued my interests. i have huge report that uses power query into a pivot table but its size is 85mg and takes 15 minutes to refresh the data from the server. i'm going to translate all to power pivot.
Hi there, this is amazing. I do have a question. i have already loaded 9000 records into the power pivot and built a dashboard based on it, it is a monthly report. but now problem the is, each month i have new sales data. in that case, how to just load or add the data to the table that 's already in the power pivot??? cause i tried many ways, it would just add the data to a different tab, like a new table, but that is not how i want it. please help. thanks in advanced.
If the table was created by Power Query you can simply append the new data. If not, you need to use a Power Query to create the original table.
@@ExcelOlympics oh no... really, what i did was adding the data in excel to data model directly, did not use the power query. and , i deleted the orginal data in excel. so bascalliy now, the power pivot has the data, and I build the report based on the data on the power pivot. oh my god, so, does that mean there is no way i can append the new data to existing data model without using power query??
so i have to use the power query to load the data to data model and rebuild everything again???
Well the "I deleted the original data" could be a problem. But you do not need to build the whole thing again. You bring in the table again, this time with Power Query. You then connect the table to the model and edit all the measures so that they point to the new table. And in theory everything should work, even the Pivot Tables... This would allow you to then delete the original table.
best
Thanks
I find power pivot always causes excel crash.😂
Well it certainly was known for that back in the day but it has made great strides after 2016 and is very stable now.