❓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!
This is great, and what timing! I do almost all of my daily data analysis using Power BI, so I questioned if I should take the time to watch this video. But just 2 days later I was asked to do a quick analysis using an Excel extract as a data source, so I quickly did in Excel, using Power Pivot, what I'd normally do in Power BI. I wouldn't have been sure how to do that so quickly if I hadn't just seen this video. And if I discover that I'll need to repeat that analysis often, I could copy the queries I created in the Excel file's Power Query to a Power BI report or Dataflow and not have to redo anything. Brilliant!
@@bermchasin No. There's already so much to learn in the Power BI ecosystem (not to mention Excel), and that's done what I've needed, so I haven't had extra bandwidth.
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!
Hi Mynda. Thank you for this tutorial. I can tell you all "Power" features of Excel are so addictive that sometines I forget to use ol'good formulas 😉. And some tasks are easier to solve with Power tools than classic methods.
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!!
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!
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
7:50 Hi Mynda IMHO we should always use Fully Qualified Column Names when referring to a table column. It's about readability when using references w/o table names are used for measures. It's a kind of best practice I would say. On the other hand, I would prefer iterator functions before calculated columns. It's performing better if I'm not wrong.
I agree, in measures fully qualified table references. In calculated columns it's not necessary. Iterator functions are more efficient but they're not available for every calculation you may want to do, in which case, you can add the calculated column in Power Pivot, or if you're using Power Query, do it there before loading the data to Power Pivot.
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.
This is great and I will explore and use. It seem like MSFT keeps adding and content creators are emphasizing database functions in Excel - is there any hope for the Access database program?
Im just basic user of excel but recently i know about power bi, power pivot and dax . Can anyone please suggest what should i learn 1st? And from where i can learn it from basic to advanced?? Please help me in this to learn simple way and faster way
Power BI uses Power Query to get and clean the data, and Power Pivot to model it. Power Query and Power Pivot are also available in Excel, so learning these skills first will get you up and running with Power BI fast. You can get up to speed fast with my 3 course bundle (Power BI + Power Query + Power Pivot) here: www.myonlinetraininghub.com/power-bi-course
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.
Remarks would have to go in the PivotTable source data table. You can't add anything to a PivotTable or even beside it and make it stay in line with the data after refresh.
What is the best way to handle a sheet that has lots of rows and data. In my case, the formula and calculations are not done and not updated. So I had to increase iterations to make excel do the formulas. Can you give us the best ways to handle big files? Like which excel file format to use, ..etc
I would use Power Query to get the data from one Excel file, clean it, add calculations as required and then load it to the Power Pivot data model. Here's a demo of Power Query in action: th-cam.com/video/Nbhd0B5ldJE/w-d-xo.html
Efficiency will depend on many factors. Technically, there is no limit to the amount of data you can load to the Pivot cache because it isn't limited by the rows in the worksheet itself. I suggest, if you're working with a lot of data, you load it to Power Pivot and make sure you're running 64-bit Excel. Also, move any calculated columns to Power Query to do before loading the data to the Power Pivot data model.
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.
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
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.
Love the video - but I’m being thick. How do you add ‘Table design’ to the headings … I’m just trying to get familiar with all this good stuff and want to replicate the training video on my laptop. Don’t even know what it’s called. Toolbar / ribbon etc. lol Hope someone can shove me in the right direction!
The Table Design tab appears when you have a cell selected in an Excel Table. You first have to format your data as a Table. You can use the keyboard shortcut, CTRL+T to do this.
13:45 - you can update your reports with 1 click.... well, except for those manual numbers you entered for your KPI. Surely would be better to use a linked value.
You can't build a single 'regular' PivotTable from multiple individual tables. You first have to consolidate them into one table with said common column. However, with Power Pivot, you don't need to first consolidate them.
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)?
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
I'd recommend using Power Query to connect direct to the SQL server and fetch the data (no need for VBA), but this video was just about introducing Power Pivot. I've got other videos on Power Query that you should check out if you're still using VBA to automate data gathering. This video demos using Power Query to get multiple files from a folder: th-cam.com/video/Nbhd0B5ldJE/w-d-xo.html
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
I guess 'data model' is more universally known, whereas 'Power Pivot' is a brand if you like. Also, it is thought that the word 'Power' is a deterrent to users who may feel it will be too advanced to even try.
@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.
In Power Query I have a data model of 30.000 rows or more, by combining multiple excel files. In power Query it take up to 15 minutes just to load the preview for every step I add, and when I try to load it in my data model (whithout saving it in excel, but as a connection only), it take over 2h and a half to load in the data model. How can I work in these conditions?
30000 rows isn't the reason it's slow because this is a relatively small dataset. It'll be the way the query is written that's causing the slowness. This post has some suggestions for speeding up slow queries: www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-times-in-power-query
The problem I have is that when importing data from folder, it does not put it in a link but puts it into the file making it a really large file. The data spans 5 years so files are large the pivot file comes in at 125mb. It is easy when linking single files as power pivot saves it as a link
That's easily fixed. Use Power Query to get the data from your external files (Excel, CSV, Text etc.) and when you 'Close & Load to...' choose 'Only Create Connection' and 'Load to Data Mode'. This will load the data direct to Power Pivot and skip also loading it to a sheet in the file.
It shouldn't make any difference where the data comes from. You just need to choose 'Close & Load To...' when first closing the query. Then you can specify where to load it to.
Access is a database, Power Pivot is a reporting too that stores data from other sources in a relational model and enables you to create reports using PivotTables. It is also available in Power BI.
10:00 Wait, I’m confused… So, first you start by creating a calculated column, but now you have a measure (?) Why not just create a measure to begin with?
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.
When calculating at the row level of detail, you use Calculated Columns. There are some measures that will iterate over rows like SUMX and other functions ending in 'X', but not all have this option. As this is an introductory video for Power Pivot, I intentionally kept it simple.
I love your content so much. You explain things really well but the vocal fry is very quite grating. I thought to myself "Why aren't I subbed??? This is great" and then I realised why :). Just some feedback.
Interesting. I've never heard the term 'vocal fry' and had to do some research. I have a medical condition that affects my voice from time to time. It's a shame you can't see past it to enjoy my free tutorials and benefit from the content. I do hope that your reason for finding annoyance with my voice isn't as argued by Cambridge University's research paper "Girl Talk: Understanding Negative Reactions to Female Vocal Fry" where they state "when fry is heard as annoying and distracting, it is because the hearer interprets the speaker as echoing an utterance from a position of authority to which she is not entitled. We show that this reaction encodes conscious or unconscious sexist attitudes toward women's voices." Citation: www.cambridge.org/core/journals/hypatia/article/abs/girl-talk-understanding-negative-reactions-to-female-vocal-fry/AFD04ACEFCD0A0F5C08511AA9EA10504
@@MyOnlineTrainingHub I regret it too - I otherwise did enjoy your content. I'm also sorry for your medical condition. That said, I think taking it to a place centered around gender is weird and awkward. Vocal fry is grating from both men and women so my feedback was in no way related to you being a female. (That take is totally new to me). Raising that has added a layer of ick which was unexpected.
Not necessarily. Power Pivot overcomes many limitations of regular PivotTables with the DAX formula language. However, if you're just doing basic summarisations and aren't finding regular PivotTables limiting, then there's no reason to stop using them.
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.
@@MyOnlineTrainingHub What do you mean by 'flexibility?' Could you give an example on this? (maybe it would be a good idea to make a video 'Why you should stick to Excel + Power Pivot and NOT move to MS Access')
It is the same! The same Power Pivot data model is in Excel and Power BI. It was originally developed in Excel, then when they created Power BI, they used the same Power Pivot data model and DAX formula language.
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.
"nowadays we need/../"? You and I do not know the actual needs of others. I suppose this is a great functionality if you work in Excel a lot. A powerful tool does not equal a need.
Thanks for watching! When I said 'we,' I was referring broadly to the increasing demand for tools like Power Pivot among Excel users tackling larger or more complex datasets. It wasn’t meant to speak for everyone’s specific needs, but rather to highlight why this functionality has become important for many. Of course, not every tool is a 'need' for everyone, but for those who work extensively in Excel, Power Pivot offers capabilities that regular PivotTables simply can’t match.
@MyOnlineTrainingHub indeed. I was just in a semantic kind of mood and got hung up on the wording. Better tools are always good for those who can find a use for them. The video was quite good.
Thanks for watching. Not everyone has heard of Power Pivot and Microsoft themselves admit the term “Power” is daunting to many users, so I chose not to use it in the title to encourage as many people to learn about this tool as possible.
so you still used pivot tables at the end. You used the power pivot (which is old tech not new tech ). Power query is newer than power pivot. You can do all those mappings in a power query without loading it in the power pivot. Pointless video from my perspective I clicked on the video thinking i can use something else instead of pivot tables
Great to hear you’re up to speed with the Power tools. You’d be surprised how many people haven’t used Power Pivot yet. I don’t recommend using Power Query to do this as it results in a bigger file than if you use 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.
Not conviced at all, you introduce database theory to people who just expect simple and clear Excel. 14 min of explanations confuse me, and you sound as Microsoft personnel, sorry.
This video is meant for those who are already comfortable with PivotTables and looking to explore more advanced techniques. Maybe the Power Query playlist will be more helpful.
❓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.
Wait til you find out about python lol
@@daillengineer yes!! Just finished Python this semester - life altering!
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.
Wait til you find out about Python and streamlit
I don't think anyone explains this better than you. Always learning!
Thanks for your kind words, Eliot!
really 🎉❤
This is great, and what timing! I do almost all of my daily data analysis using Power BI, so I questioned if I should take the time to watch this video. But just 2 days later I was asked to do a quick analysis using an Excel extract as a data source, so I quickly did in Excel, using Power Pivot, what I'd normally do in Power BI. I wouldn't have been sure how to do that so quickly if I hadn't just seen this video. And if I discover that I'll need to repeat that analysis often, I could copy the queries I created in the Excel file's Power Query to a Power BI report or Dataflow and not have to redo anything. Brilliant!
Yes, yes, yes! 😁 So pleased this was helpful and even better that you know you can copy the queries from Excel to Power BI.
have you ever tried python / pandas?
@@bermchasin No. There's already so much to learn in the Power BI ecosystem (not to mention Excel), and that's done what I've needed, so I haven't had extra bandwidth.
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 🙌
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. 🙏
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!
Hi Mynda. Thank you for this tutorial. I can tell you all "Power" features of Excel are so addictive that sometines I forget to use ol'good formulas 😉. And some tasks are easier to solve with Power tools than classic methods.
😁 there are so many options these days that it's tricky to choose what to use!
Great ! Awesome explanations as always.
Thanks Mynda and happy new year !
God bless you
You are very welcome! And happy new year to you too!
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 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!
Yes, Power Pivot. Ready to devour this video. Love your Pivot tables and Data Model content
Awesome, thank you!
Nice video, feels like a proper lesson. I did not know about the KPI tool, thanks!
You're welcome! Hope you find it useful.
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!
Thanks so much, this was perfect timing for a current project I’m working on. Another excellent tutorial 🔥
Awesome to hear!
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 😅
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.
7:50 Hi Mynda IMHO we should always use Fully Qualified Column Names when referring to a table column. It's about readability when using references w/o table names are used for measures. It's a kind of best practice I would say. On the other hand, I would prefer iterator functions before calculated columns. It's performing better if I'm not wrong.
I agree, in measures fully qualified table references. In calculated columns it's not necessary. Iterator functions are more efficient but they're not available for every calculation you may want to do, in which case, you can add the calculated column in Power Pivot, or if you're using Power Query, do it there before loading the data to Power Pivot.
Reminds me of my days programming in MS Access. Great to see this function is integrated in excel now.
Yes! Power Pivot has been available in Excel since 2010! Hope you can make use of it.
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. 😉
Excelent as usual. Congratulations!
Cheers, I'm glad you liked it!
Thanks, turning on the options is something I have done
Glad I could help!
How did you calculate the average adjusted profit when creating KPI? Do we have to manually calculate and enter the value?
I had the average from the measure I created prior.
Your videos are so helpful!
I'm glad you find them useful! 🙏
I learned something new today! Great video
Awesome to hear!
excellent tutorial as always
Thanks again!
Thank you! This is great!
Glad you liked it!
Great video, very helpful. thanks :)
Awesome to hear 🙏
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
This is great and I will explore and use. It seem like MSFT keeps adding and content creators are emphasizing database functions in Excel - is there any hope for the Access database program?
Not much hope for Access these days. They won't get rid of it, but it's not being developed.
Im just basic user of excel but recently i know about power bi, power pivot and dax . Can anyone please suggest what should i learn 1st? And from where i can learn it from basic to advanced?? Please help me in this to learn simple way and faster way
Power BI uses Power Query to get and clean the data, and Power Pivot to model it. Power Query and Power Pivot are also available in Excel, so learning these skills first will get you up and running with Power BI fast.
You can get up to speed fast with my 3 course bundle (Power BI + Power Query + Power Pivot) here: www.myonlinetraininghub.com/power-bi-course
super exciting!! cannot wait to play with this dataset. Where is the "director/director gender" sourced from?
Great to hear. I don't know where they got the gender info from.
@@MyOnlineTrainingHub okay - I think I will just a disclaimer to my data portfolio so people don't come after me if they are "gender non-conforming"
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.
Thanks. How can i add a remarks column near pivot row? I want it to remain even after pivot refresh?
Remarks would have to go in the PivotTable source data table. You can't add anything to a PivotTable or even beside it and make it stay in line with the data after refresh.
Power Pivot is really great tool!
It sure is 😁
What is the best way to handle a sheet that has lots of rows and data. In my case, the formula and calculations are not done and not updated. So I had to increase iterations to make excel do the formulas. Can you give us the best ways to handle big files? Like which excel file format to use, ..etc
I would use Power Query to get the data from one Excel file, clean it, add calculations as required and then load it to the Power Pivot data model. Here's a demo of Power Query in action: th-cam.com/video/Nbhd0B5ldJE/w-d-xo.html
Outstanding!
Glad you enjoyed it!
Recent subscriber, big fan
Awesome! Welcome 👋
Good info thanks
Glad you found it helpful.
Thanks for sharing 👍
Thanks for watching 😁
This was great now I understand kpi.
Have fun with them!
Can u do most used functions - calculate - related - filter - concatenation- time ?
Yes. DAX has even better time intelligence functions than we have in Excel.
What is the maximum data volume (rows x columns) and file size Excel can handle efficiently with Pivot Table?
Efficiency will depend on many factors. Technically, there is no limit to the amount of data you can load to the Pivot cache because it isn't limited by the rows in the worksheet itself. I suggest, if you're working with a lot of data, you load it to Power Pivot and make sure you're running 64-bit Excel. Also, move any calculated columns to Power Query to do before loading the data to the Power Pivot data model.
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!
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
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.
My favorite AI teacher. She speaks without even taking a breath.
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.
Love the video - but I’m being thick. How do you add ‘Table design’ to the headings … I’m just trying to get familiar with all this good stuff and want to replicate the training video on my laptop. Don’t even know what it’s called. Toolbar / ribbon etc. lol Hope someone can shove me in the right direction!
Sorted - i was thick!
The Table Design tab appears when you have a cell selected in an Excel Table. You first have to format your data as a Table. You can use the keyboard shortcut, CTRL+T to do this.
Thank you - perfect - you are a great help - I appreciate the time you have taken to respond 👍
13:45 - you can update your reports with 1 click.... well, except for those manual numbers you entered for your KPI. Surely would be better to use a linked value.
Absolutely, you can write a measure that automatically calculates 👍
You can link several tables in regular Pivot. Just need a common column.
You can't build a single 'regular' PivotTable from multiple individual tables. You first have to consolidate them into one table with said common column. However, with Power Pivot, you don't need to first consolidate them.
@ how about tables linked by DATA>RELATIONSHIPS? Isn’t that the same thing? I wasn’t sure if same concept. Thanks!
Linked tables via relationships is Power Pivot/Data Model, not regular PivotTables.
2:06 OR - You would have to use Power Query to merge the data into the other table…
Yep, that's another option 👍
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.
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
just switch to Power BI. using Excel this way is like trying to hang a painting with a shovel. does it work? yes. does it make sense? perhaps no
Does Power Pivot work in Excel for Mac? If so, how do I install it?
No. You can't use Power Pivot on a Mac unless you use Parallels to install Windows OS and then Windows version of Excel.
why not use vba and use SQL queries to fetch the data?
I'd recommend using Power Query to connect direct to the SQL server and fetch the data (no need for VBA), but this video was just about introducing Power Pivot. I've got other videos on Power Query that you should check out if you're still using VBA to automate data gathering. This video demos using Power Query to get multiple files from a folder: th-cam.com/video/Nbhd0B5ldJE/w-d-xo.html
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
Why does the ribbon interface display the generic “Data Model” if it really means “PowerPivot” ???
I guess 'data model' is more universally known, whereas 'Power Pivot' is a brand if you like. Also, it is thought that the word 'Power' is a deterrent to users who may feel it will be too advanced to even try.
@@MyOnlineTrainingHubGood points! I just think that it would be more intuitive with consistent nomenclature.
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.
In Power Query I have a data model of 30.000 rows or more, by combining multiple excel files. In power Query it take up to 15 minutes just to load the preview for every step I add, and when I try to load it in my data model (whithout saving it in excel, but as a connection only), it take over 2h and a half to load in the data model. How can I work in these conditions?
30000 rows isn't the reason it's slow because this is a relatively small dataset. It'll be the way the query is written that's causing the slowness. This post has some suggestions for speeding up slow queries: www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-times-in-power-query
The problem I have is that when importing data from folder, it does not put it in a link but puts it into the file making it a really large file. The data spans 5 years so files are large the pivot file comes in at 125mb. It is easy when linking single files as power pivot saves it as a link
That's easily fixed. Use Power Query to get the data from your external files (Excel, CSV, Text etc.) and when you 'Close & Load to...' choose 'Only Create Connection' and 'Load to Data Mode'. This will load the data direct to Power Pivot and skip also loading it to a sheet in the file.
@@MyOnlineTrainingHub this works when importing a single file, but does not seem to work when using and combining files from a folder (import Folder)
It shouldn't make any difference where the data comes from. You just need to choose 'Close & Load To...' when first closing the query. Then you can specify where to load it to.
When I am trying to Create KPI, I got an unhandled exception error, JIT error. How to solve this issue?
Not sure. You'd have to copy the error into Google/Bing and look for a solution, sorry.
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
What’s the difference between Power Pivot and Access?
Access is a database, Power Pivot is a reporting too that stores data from other sources in a relational model and enables you to create reports using PivotTables. It is also available in Power BI.
Excel with Power Pivot and Power BI = Modern day data analysis sage!
😁
Does it work in excel 2019?
Yep 😊
I guess if they are getting rid of Access is is good to put the code somewhere.
I wouldn't say they're getting rid of Access, it's just not being developed much anymore.
10:00 Wait, I’m confused… So, first you start by creating a calculated column, but now you have a measure (?) Why not just create a measure to begin with?
See previous reply 😉
But not available on Microsoft 365?
Yes. Available in 365. That’s what I’m using in the video.
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.
7:19 Why calculated columns and not measures??
When calculating at the row level of detail, you use Calculated Columns. There are some measures that will iterate over rows like SUMX and other functions ending in 'X', but not all have this option. As this is an introductory video for Power Pivot, I intentionally kept it simple.
I love your content so much. You explain things really well but the vocal fry is very quite grating. I thought to myself "Why aren't I subbed??? This is great" and then I realised why :). Just some feedback.
Interesting. I've never heard the term 'vocal fry' and had to do some research. I have a medical condition that affects my voice from time to time. It's a shame you can't see past it to enjoy my free tutorials and benefit from the content.
I do hope that your reason for finding annoyance with my voice isn't as argued by Cambridge University's research paper "Girl Talk: Understanding Negative Reactions to Female Vocal Fry" where they state "when fry is heard as annoying and distracting, it is because the hearer interprets the speaker as echoing an utterance from a position of authority to which she is not entitled. We show that this reaction encodes conscious or unconscious sexist attitudes toward women's voices."
Citation: www.cambridge.org/core/journals/hypatia/article/abs/girl-talk-understanding-negative-reactions-to-female-vocal-fry/AFD04ACEFCD0A0F5C08511AA9EA10504
@@MyOnlineTrainingHub I regret it too - I otherwise did enjoy your content. I'm also sorry for your medical condition. That said, I think taking it to a place centered around gender is weird and awkward. Vocal fry is grating from both men and women so my feedback was in no way related to you being a female. (That take is totally new to me). Raising that has added a layer of ick which was unexpected.
Yes But, If there is no unique values ( or in unique contains duplicates) it is better to go with pivot table
Not necessarily. Power Pivot overcomes many limitations of regular PivotTables with the DAX formula language. However, if you're just doing basic summarisations and aren't finding regular PivotTables limiting, then there's no reason to stop using them.
What about power query then?
Yep, plenty of videos on Power Query in my playlist here: th-cam.com/play/PLmd91OWgLVSKnVrL0YxdOH61MAiqlFHac.html
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.
😎😎😎
😁🙏
6:33 - What!?!?!?!? No one in the history of ever has had this problem????? ;)
😂
❤
🙏
Ok, but why mimic database functionality in a datasheet?
I think it would be much simpler just convert your Excel file to an MS Access database.
Because Access doesn’t have the flexibility of Excel.
@@MyOnlineTrainingHub What do you mean by 'flexibility?' Could you give an example on this?
(maybe it would be a good idea to make a video 'Why you should stick to Excel + Power Pivot and NOT move to MS Access')
Very similar to Power BI. Except the power of the relationships it seems.
It is the same! The same Power Pivot data model is in Excel and Power BI. It was originally developed in Excel, then when they created Power BI, they used the same Power Pivot data model and DAX formula language.
peace be upon you from me and zamzam water
Too bad this is not available in Excel for Mac ...
If you use Excel a lot, Power Pivot and Power Query are worth having a PC for.
You can install windows on Macs via bootcamp or an emulator. This is what I use for Excel (and it allows you to use the shortcuts you may know)
Really no different than building a crosstab query in Access. Excel has essentially replaced Access because most folks don't understand Access.
Yes, very similar to access.
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
"nowadays we need/../"? You and I do not know the actual needs of others. I suppose this is a great functionality if you work in Excel a lot. A powerful tool does not equal a need.
Thanks for watching! When I said 'we,' I was referring broadly to the increasing demand for tools like Power Pivot among Excel users tackling larger or more complex datasets. It wasn’t meant to speak for everyone’s specific needs, but rather to highlight why this functionality has become important for many. Of course, not every tool is a 'need' for everyone, but for those who work extensively in Excel, Power Pivot offers capabilities that regular PivotTables simply can’t match.
@MyOnlineTrainingHub indeed. I was just in a semantic kind of mood and got hung up on the wording. Better tools are always good for those who can find a use for them. The video was quite good.
The title should include the name "Power Pivot," otherwise this will almost act as a click-bait.
Thanks for watching. Not everyone has heard of Power Pivot and Microsoft themselves admit the term “Power” is daunting to many users, so I chose not to use it in the title to encourage as many people to learn about this tool as possible.
People still use Excel?
so you still used pivot tables at the end. You used the power pivot (which is old tech not new tech ). Power query is newer than power pivot. You can do all those mappings in a power query without loading it in the power pivot. Pointless video from my perspective
I clicked on the video thinking i can use something else instead of pivot tables
Great to hear you’re up to speed with the Power tools. You’d be surprised how many people haven’t used Power Pivot yet. I don’t recommend using Power Query to do this as it results in a bigger file than if you use Power Pivot.
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.
12:17 So, you are forcing your using to scroll through that entire list of directors… I don’t think this is good design.
You can add the field as a filter and search for them if you prefer. Again, this is a demonstration of Power Pivot functionality for beginners. 😁
,,,,,
Joke
Not conviced at all, you introduce database theory to people who just expect simple and clear Excel.
14 min of explanations confuse me, and you sound as Microsoft personnel, sorry.
This video is meant for those who are already comfortable with PivotTables and looking to explore more advanced techniques. Maybe the Power Query playlist will be more helpful.