❓What’s the biggest dataset you’ve tried to manage in Excel? Download the example file here and follow along: bit.ly/powerpivot24file Learn more about the Power Pivot and DAX course: bit.ly/powerpivot24course
@@MyOnlineTrainingHub The biggest one to date is around 330k rows + additional data tables with up to 15k rows which needed Power Query and Pivot to be handled properly. The result is a workbook with several dashboards for lots of different sales aspects.
My largest dataset was World Bank data which showed how much every country in the world exported to all the other countries for over two hundred different food items each for the the years 1962 to 2021. It was millions of rows and the first time my Power Query got notably slow.
I took your Power Pivot and Power Query course a couple of years ago and it changed everything! I saved so much time at work for myself and my coworkers. I get excited when someone brings me data from multiple sources that needs major cleaning, especially when they think it's going to be so tedious to sift through, because I have so many more tools now that make it much easier.
🥰Congratulations on your success with Power Query & Power Pivot! I love hearing success stories like this. I'm so pleased my courses helped you get started.
Awesome Mynda. Once I learned Power Pivot (and DAX) several years ago it completely transformed my capabilities and what I was able to accomplish in my work. I think so many Excel users still don't take advantage of what Power Pivot/DAX can do. Great video!
As a plant controller, I used Power Pivot calculated measures to generate a gross profit margin based on the revenue, material, labor and overhead and helped the company improved top line revenue from the making price increase decisions on specific product models. It has modeling functionality that eliminates the need to do vlookups. This is a great tool for anyone doing data wrangling!
Mind. Blown. There is sooo much to learn from in this video, and I've been hoovering up your insights for a few months now. This ties several key tools together - I'm confident I can pull out superior information from our datasets than the native system interfaces now. Thank you, Mynda!
Wonderful Mynda. I am using Data model for reporting, but this technique never used till now. Really great technique and how simply explained. Two thumps up!!
Hi Mynda Impressive, and I would even allow myself to say that I am quite well versed in Power Pivot, but there is always something good to be gained from your review. The formula with Adjusted profit is great, I hadn't come up with it myself, thanks
Excellent video, tried and got successful.. Need a video on P&L template in Power bi with Gross profit, operating profit and net profit shown just like in excel.
Yes thanks! This is great stuff, am using it for approx. 10 years already. In the early days, it wasn't that stable yet unfortunately. It often gave errors when opening the file (due to the file allegedly not being saved correctly), or giving memory error messages whilst there was plenty (32Gb), but they seem to have fixed that by now. I work for retailers (data per product/per day etc., including history) and I believe one ot the largest sales datadump contained 30m+ records.
@@MyOnlineTrainingHub Thanks! And yes, I think the PBI / Power Pivot add-in is the greatest improvement they did on Excel since its beginning. Greetings from the NL!
Hello Mynda. I noticed recently that you can NOT group elements of a field manually when the table is in the Data Model (aka Power Pivot). Like you have a column with countries, and you want to group some from a given region, say, those in the European Union and the others. Excel will not let you do that. I agree that you should have a linked table with your countries and what group(s) they belong to, which is what I do in this case, but that may be overkill in some other situations.
Yeah, this is one of the differences with Power Pivot, but like you say, the correct way is to create a dimension table containing the grouping, overkill or not.
Dearest, How to compare data in pivot table? Ex: sale in Jan 2023 vs sale in Jan 2024? Calculate to see variance amount and percentage. Appreciate for your teaching 🙏
Like @hi_vishy said, you can use DAX. Power Pivot has time intelligence functions like SAMEPERIODLASTYEAR etc. you can use to write measures for use in Power Pivot PivotTables.
Hi Mynda Been meaning to raise this for a while. I'm becoming increasingly frustrated with PQ/PP/DAX/VBA. Excel should come with a quality warning sticker. Compared to PowerBI, I find excel quite flakey when you push it as hard as I do. I have to keep regular backups as it semi regularly corrupts and the data model, VBA modules need to be recreated as they are deleted from the repaired file. I have a heavy duty pc with loads of processing power and RAM. I'm rapidly approaching the point where I'm not convinced that building complex financial models in Excel is particularly helpful. Mind you, it's still vastly more reliable than the blue screen of death in Office XP and earlier 🙂 Do you have any observations on this?
It's difficult to say much without being familiar with your file. There are some tips on improving Power Query performance here: www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-times-in-power-query As for Power Pivot, I assume your model is star schema. As for VBA, I don't have anything constructive, sorry. If you'd like me to refer you to someone who can analyse your file and identify areas for improvement, reach out via email: website @myonlinetraininghub.com
Wondering is there a preference of Calculated Columns vs Measures. Or would there be different use cases for both. It seems to me that their uses kinda overlap.
Why in KPI you needed to add absolute value (of the average value)? Is it possible to make KPI dynamic (because value of average will change when you add new data to your table)?
I use PowerPivot from time to time, but in most cases I find it too complex for quick and easy use. Usually a simple PivotTable is all you (or at least I) need.
@remuslupinhp "More brand name less functionality" 😂 well said. @UdaiKs you can install Parallels and install Windows OS there with Excel for Windows and full functionality, or get a PC.
I'm not sure I understand what you're asking here. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi, I tried to create relationship between 2 power pivot tables but when I insert pivot table (based on data model) i wasn't able to get the related fields into it. I am currently using RelatedFields. Any idea what could be the issue?
Impossible to say without seeing your file. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Your teaching is great, but I will appreciate if you use highlighted pointer & focus on viewers who watch your lessons on mobile phones. (Tiny screen)Your subscribers will increase considerably if you take that initiative. As of now, I may or may not view your presentations as an option. I hope you understand what I mean, & take this feedback in the right spirit.
Thanks for the feedback. I prefer to set my mouse pointer size to 200% and smooth the action to make it easier to follow. Having the pointer highlighted can also be a distraction from where you're supposed to be focusing, so it's difficult to balance.
Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns i.e 20/11/2024 Diner to 24/11/24 Breakfast BF (Column) 4nos Lunch (Column) 3nos Diner (Column) 4nos If you give me a solution i will be very greatful.
Thanks for watching my video! Regular PivotTables can't use data from multiple tables without first merging the tables into one. Power Pivot PivotTables are the only type of PivotTable that can use data from multiple tables in a single PivotTable.
❓What’s the biggest dataset you’ve tried to manage in Excel?
Download the example file here and follow along: bit.ly/powerpivot24file
Learn more about the Power Pivot and DAX course: bit.ly/powerpivot24course
@@MyOnlineTrainingHub The biggest one to date is around 330k rows + additional data tables with up to 15k rows which needed Power Query and Pivot to be handled properly. The result is a workbook with several dashboards for lots of different sales aspects.
Please do more dashboards
Ma'am do you have any discount policy on your courses for those who can't afford to pay all fees.
@@MyOnlineTrainingHub biggest dataset I have loaded into excel, 28 columns, 1.1 million lines
My largest dataset was World Bank data which showed how much every country in the world exported to all the other countries for over two hundred different food items each for the the years 1962 to 2021. It was millions of rows and the first time my Power Query got notably slow.
I took your Power Pivot and Power Query course a couple of years ago and it changed everything! I saved so much time at work for myself and my coworkers. I get excited when someone brings me data from multiple sources that needs major cleaning, especially when they think it's going to be so tedious to sift through, because I have so many more tools now that make it much easier.
🥰Congratulations on your success with Power Query & Power Pivot! I love hearing success stories like this. I'm so pleased my courses helped you get started.
Awesome Mynda. Once I learned Power Pivot (and DAX) several years ago it completely transformed my capabilities and what I was able to accomplish in my work. I think so many Excel users still don't take advantage of what Power Pivot/DAX can do. Great video!
Thanks for watching, Chris. Great to hear you embraced it.
I don't think anyone explains this better than you. Always learning!
Thanks for your kind words, Eliot!
really 🎉❤
As a plant controller, I used Power Pivot calculated measures to generate a gross profit margin based on the revenue, material, labor and overhead and helped the company improved top line revenue from the making price increase decisions on specific product models. It has modeling functionality that eliminates the need to do vlookups. This is a great tool for anyone doing data wrangling!
It’s so great to hear how Power Pivot has helped you achieve success! Congratulations 🙌
Mind. Blown. There is sooo much to learn from in this video, and I've been hoovering up your insights for a few months now. This ties several key tools together - I'm confident I can pull out superior information from our datasets than the native system interfaces now. Thank you, Mynda!
Awesome to hear, Dan!
You are so kind and a great teacher showing alternate methods for same goals. THANKS SO MUCH. You are always great!
Thank you! I'm glad you're finding the alternative methods helpful. 🙏
This really changes how I'm thinking about creative options for displaying KPIs. Thank you!
Awesome, glad you're finding new ways to use these tools!
5:25 “PivotTable … we know and love…” What a sense of humour 😂 I never get PTs to look how I want them.
😁you might like this video then: th-cam.com/video/5kUQSxBVlZ8/w-d-xo.html
Thanks, turning on the options is something I have done
Glad I could help!
Excelent as usual. Congratulations!
Cheers, I'm glad you liked it!
Thanks so much, this was perfect timing for a current project I’m working on. Another excellent tutorial 🔥
Awesome to hear!
Wonderful Mynda. I am using Data model for reporting, but this technique never used till now. Really great technique and how simply explained. Two thumps up!!
Awesome to hear you can make use of it! 🙏
Yes, Power Pivot. Ready to devour this video. Love your Pivot tables and Data Model content
Awesome, thank you!
Hi Mynda
Impressive, and I would even allow myself to say that I am quite well versed in Power Pivot, but there is always something good to be gained from your review.
The formula with Adjusted profit is great, I hadn't come up with it myself, thanks
I'm so pleased someone with your skill level is still finding tidbits in my videos 😅
Your videos are so helpful!
I'm glad you find them useful! 🙏
This is huge. I have so many users who need to watch this.
So pleased it was helpful. Please feel free to share it with them.
Excellent video, tried and got successful.. Need a video on P&L template in Power bi with Gross profit, operating profit and net profit shown just like in excel.
I’m so pleased you found this helpful! I can't do every topic, but I'll keep your suggestion in mind. 😉
Yes thanks! This is great stuff, am using it for approx. 10 years already.
In the early days, it wasn't that stable yet unfortunately. It often gave errors when opening the file (due to the file allegedly not being saved correctly), or giving memory error messages whilst there was plenty (32Gb), but they seem to have fixed that by now.
I work for retailers (data per product/per day etc., including history) and I believe one ot the largest sales datadump contained 30m+ records.
Try using 64 bit excel
30M records, wow! It's awesome to hear you're an early adopter of Power Pivot.
@@MyOnlineTrainingHub Thanks! And yes, I think the PBI / Power Pivot add-in is the greatest improvement they did on Excel since its beginning.
Greetings from the NL!
Recent subscriber, big fan
Awesome! Welcome 👋
Power Pivot is really great tool!
It sure is 😁
This was great now I understand kpi.
Have fun with them!
Thanks for sharing 👍
Thanks for watching 😁
Hello Mynda. I noticed recently that you can NOT group elements of a field manually when the table is in the Data Model (aka Power Pivot). Like you have a column with countries, and you want to group some from a given region, say, those in the European Union and the others. Excel will not let you do that. I agree that you should have a linked table with your countries and what group(s) they belong to, which is what I do in this case, but that may be overkill in some other situations.
Yeah, this is one of the differences with Power Pivot, but like you say, the correct way is to create a dimension table containing the grouping, overkill or not.
Dearest,
How to compare data in pivot table?
Ex: sale in Jan 2023 vs sale in Jan 2024? Calculate to see variance amount and percentage. Appreciate for your teaching 🙏
Use dax
Like @hi_vishy said, you can use DAX. Power Pivot has time intelligence functions like SAMEPERIODLASTYEAR etc. you can use to write measures for use in Power Pivot PivotTables.
Hi Mynda
Been meaning to raise this for a while.
I'm becoming increasingly frustrated with PQ/PP/DAX/VBA. Excel should come with a quality warning sticker.
Compared to PowerBI, I find excel quite flakey when you push it as hard as I do. I have to keep regular backups as it semi regularly corrupts and the data model, VBA modules need to be recreated as they are deleted from the repaired file.
I have a heavy duty pc with loads of processing power and RAM.
I'm rapidly approaching the point where I'm not convinced that building complex financial models in Excel is particularly helpful.
Mind you, it's still vastly more reliable than the blue screen of death in Office XP and earlier 🙂
Do you have any observations on this?
It's difficult to say much without being familiar with your file. There are some tips on improving Power Query performance here: www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-times-in-power-query
As for Power Pivot, I assume your model is star schema. As for VBA, I don't have anything constructive, sorry.
If you'd like me to refer you to someone who can analyse your file and identify areas for improvement, reach out via email: website @myonlinetraininghub.com
Does Power Pivot work in Excel for Mac? If so, how do I install it?
Is it better to add calculated columns in Power Pivot or Power Query, or doesn't it matter?
Ah, I suppose it depends if the column refers to more than one table?🤔
It's better to do it in Power Query.
Wondering is there a preference of Calculated Columns vs Measures. Or would there be different use cases for both. It seems to me that their uses kinda overlap.
Different use cases, but where possible use measures so that you're not adding data to your model unnecessarily.
@@MyOnlineTrainingHub Thank you!
Why in KPI you needed to add absolute value (of the average value)? Is it possible to make KPI dynamic (because value of average will change when you add new data to your table)?
Yes, you can make it dynamic by referencing another measure as mentioned before I chose the absolute value option.
Can u do most used functions - calculate - related - filter - concatenation- time ?
Yes. DAX has even better time intelligence functions than we have in Excel.
I use PowerPivot from time to time, but in most cases I find it too complex for quick and easy use. Usually a simple PivotTable is all you (or at least I) need.
Yeah, I still use regular PivotTables for basic stuff.
Can I use power pivot and query on Mac Pro ?
Mac doesn't support power pivot, Mac is not the best for power bi either... More brand name less functionality...
@remuslupinhp "More brand name less functionality" 😂 well said.
@UdaiKs you can install Parallels and install Windows OS there with Excel for Windows and full functionality, or get a PC.
Ma'am , help me for following how to merge the data in column with the same repeated data in the pivot table?
I'm not sure I understand what you're asking here. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@MyOnlineTrainingHub Ok Ma'am
Hi, I tried to create relationship between 2 power pivot tables but when I insert pivot table (based on data model) i wasn't able to get the related fields into it. I am currently using RelatedFields. Any idea what could be the issue?
Impossible to say without seeing your file. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Your teaching is great, but I will appreciate if you use highlighted pointer & focus on viewers who watch your lessons on mobile phones. (Tiny screen)Your subscribers will increase considerably if you take that initiative. As of now, I may or may not view your presentations as an option. I hope you understand what I mean, & take this feedback in the right spirit.
Thanks for the feedback. I prefer to set my mouse pointer size to 200% and smooth the action to make it easier to follow. Having the pointer highlighted can also be a distraction from where you're supposed to be focusing, so it's difficult to balance.
Hi, I have added a new measure, but I dont know why the filters can’t work
You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
❤
🙏
peace be upon you from me and zamzam water
Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns
i.e
20/11/2024 Diner to 24/11/24 Breakfast
BF (Column)
4nos
Lunch (Column)
3nos
Diner (Column)
4nos
If you give me a solution i will be very greatful.
You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Pivot tables can already be linked to multiple data sets in the same way without using power pivot. 🤷
Thanks for watching my video! Regular PivotTables can't use data from multiple tables without first merging the tables into one. Power Pivot PivotTables are the only type of PivotTable that can use data from multiple tables in a single PivotTable.
,,,,,