The Excel Tool That Does What PivotTables Can’t (File Included)

[@MyOnlineTrainingHub]

❓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

[@IsMirDochLattens]

@@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.

[@b326yr]

Please do more dashboards

[@sayeedahmed798]

Ma'am do you have any discount policy on your courses for those who can't afford to pay all fees.

[@brettburton1477]

@@MyOnlineTrainingHub biggest dataset I have loaded into excel, 28 columns, 1.1 million lines

[@ennykraft]

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.

[@OhReallySnap]

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.

[@MyOnlineTrainingHub]

🥰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.

[@daillengineer]

Wait til you find out about python lol

[@Ang7.8]

@@daillengineer yes!! Just finished Python this semester - life altering!

[@EliotPowell]

I don't think anyone explains this better than you. Always learning!

[@MyOnlineTrainingHub]

Thanks for your kind words, Eliot!

[@Dr-Emad2025]

really 🎉❤

[@chrism9037]

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!

[@MyOnlineTrainingHub]

Thanks for watching, Chris. Great to hear you embraced it.

[@daillengineer]

Wait til you find out about Python and streamlit

[@julliettecarignan8563]

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!

[@MyOnlineTrainingHub]

Yes, yes, yes! 😁 So pleased this was helpful and even better that you know you can copy the queries from Excel to Power BI.

[@merrillmchenry1450]

You are so kind and a great teacher showing alternate methods for same goals. THANKS SO MUCH. You are always great!

[@MyOnlineTrainingHub]

Thank you! I'm glad you're finding the alternative methods helpful. 🙏

[@FareyFraction]

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!

[@MyOnlineTrainingHub]

It’s so great to hear how Power Pivot has helped you achieve success! Congratulations 🙌

[@DanRoddy]

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!

[@MyOnlineTrainingHub]

Awesome to hear, Dan!

[@TheJoshtheboss]

Yes, Power Pivot. Ready to devour this video. Love your Pivot tables and Data Model content

[@MyOnlineTrainingHub]

Awesome, thank you!

[@RogerStocker]

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.

[@MyOnlineTrainingHub]

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.

[@pavol.cernak]

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.

[@MyOnlineTrainingHub]

😁 there are so many options these days that it's tricky to choose what to use!

[@FrontLineNerd]

This is huge. I have so many users who need to watch this.

[@MyOnlineTrainingHub]

So pleased it was helpful. Please feel free to share it with them.

[@jennysheets3763]

This really changes how I'm thinking about creative options for displaying KPIs. Thank you!

[@MyOnlineTrainingHub]

Awesome, glad you're finding new ways to use these tools!

[@heinzthedutch]

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.

[@hi_vishy]

Try using 64 bit excel

[@MyOnlineTrainingHub]

30M records, wow! It's awesome to hear you're an early adopter of Power Pivot.

[@heinzthedutch]

@@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!

[@loganathannainar4804]

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!!

[@MyOnlineTrainingHub]

Awesome to hear you can make use of it! 🙏

[@Rajk-z3k]

How did you calculate the average adjusted profit when creating KPI? Do we have to manually calculate and enter the value?

[@MyOnlineTrainingHub]

I had the average from the measure I created prior.

[@michaelkuhn6328]

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?

[@MyOnlineTrainingHub]

Not much hope for Access these days. They won't get rid of it, but it's not being developed.

[@TheJoshtheboss]

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.

[@MyOnlineTrainingHub]

Different use cases, but where possible use measures so that you're not adding data to your model unnecessarily.

[@TheJoshtheboss]

@@MyOnlineTrainingHub Thank you!

[@Rufus-OG]

Thanks so much, this was perfect timing for a current project I’m working on. Another excellent tutorial 🔥

[@MyOnlineTrainingHub]

Awesome to hear!

[@helvisea]

Reminds me of my days programming in MS Access. Great to see this function is integrated in excel now.

[@MyOnlineTrainingHub]

Yes! Power Pivot has been available in Excel since 2010! Hope you can make use of it.

[@ivanbork4175]

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

[@MyOnlineTrainingHub]

I'm so pleased someone with your skill level is still finding tidbits in my videos 😅

[@stanTrX]

Thanks. How can i add a remarks column near pivot row? I want it to remain even after pivot refresh?

[@MyOnlineTrainingHub]

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.

[@karllinguistik7244]

Excelent as usual. Congratulations!

[@MyOnlineTrainingHub]

Cheers, I'm glad you liked it!

[@chandrakantha648]

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.

[@MyOnlineTrainingHub]

I’m so pleased you found this helpful! I can't do every topic, but I'll keep your suggestion in mind. 😉

[@kisho2679]

What is the maximum data volume (rows x columns) and file size Excel can handle efficiently with Pivot Table?

[@MyOnlineTrainingHub]

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.

[@ricpue]

Great video, very helpful. thanks :)

[@mohamedalmusalam]

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

[@MyOnlineTrainingHub]

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: kzbin.info/www/bejne/hJPLlWN4atGXgKc

[@Ang7.8]

super exciting!! cannot wait to play with this dataset. Where is the "director/director gender" sourced from?

[@MyOnlineTrainingHub]

Great to hear. I don't know where they got the gender info from.

[@Ang7.8]

@@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"

[@GaryMillerUK]

Is it better to add calculated columns in Power Pivot or Power Query, or doesn't it matter?

[@GaryMillerUK]

Ah, I suppose it depends if the column refers to more than one table?🤔

[@MyOnlineTrainingHub]

It's better to do it in Power Query.

[@turbalejo4999]

Thank you! This is great!

[@MyOnlineTrainingHub]

Glad you liked it!

[@APG_Creativity]

Ma'am , help me for following how to merge the data in column with the same repeated data in the pivot table?

[@MyOnlineTrainingHub]

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

[@APG_Creativity]

@MyOnlineTrainingHub Ok Ma'am

[@PaulEBrownbill]

Thanks, turning on the options is something I have done

[@MyOnlineTrainingHub]

Glad I could help!

[@lowzhehao]

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?

[@MyOnlineTrainingHub]

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

[@paulvanobberghen]

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.

[@MyOnlineTrainingHub]

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.

[@zuzanasmekalova4632]

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)?

[@MyOnlineTrainingHub]

Yes, you can make it dynamic by referencing another measure as mentioned before I chose the absolute value option.

[@thebesttechnical3608]

I learned something new today! Great video

[@MyOnlineTrainingHub]

Awesome to hear!

[@JJ_TheGreat]

2:06 OR - You would have to use Power Query to merge the data into the other table…

[@MyOnlineTrainingHub]

Yep, that's another option 👍

[@stephenargent5582]

Does Power Pivot work in Excel for Mac? If so, how do I install it?

[@MyOnlineTrainingHub]

No. You can't use Power Pivot on a Mac unless you use Parallels to install Windows OS and then Windows version of Excel.

[@martins8587]

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?

[@MyOnlineTrainingHub]

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

[@andreatorriglia8010]

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

[@PaulElmont-fd1xc]

Your videos are so helpful!

[@MyOnlineTrainingHub]

I'm glad you find them useful! 🙏

[@ianrhile]

Power Pivot is really great tool!

[@MyOnlineTrainingHub]

It sure is 😁

[@sajadabdulcader5337]

Can u do most used functions - calculate - related - filter - concatenation- time ?

[@MyOnlineTrainingHub]

Yes. DAX has even better time intelligence functions than we have in Excel.

[@mogarrett3045]

excellent tutorial as always

[@MyOnlineTrainingHub]

Thanks again!

[@JJ_TheGreat]

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?

[@MyOnlineTrainingHub]

See previous reply 😉

[@nickhalden4759]

5:25 “PivotTable … we know and love…” What a sense of humour 😂 I never get PTs to look how I want them.

[@MyOnlineTrainingHub]

😁you might like this video then: kzbin.info/www/bejne/a5y4goaud7ufkJo

[@alterchannel2501]

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?

[@MyOnlineTrainingHub]

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

[@tommypain]

Outstanding!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@chakrath9149]

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 🙏

[@hi_vishy]

Use dax

[@MyOnlineTrainingHub]

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.

[@UdaiKs]

Can I use power pivot and query on Mac Pro ?

[@remuslupinhp]

Mac doesn't support power pivot, Mac is not the best for power bi either... More brand name less functionality...

[@MyOnlineTrainingHub]

@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.

[@QhYau]

When I am trying to Create KPI, I got an unhandled exception error, JIT error. How to solve this issue?

[@MyOnlineTrainingHub]

Not sure. You'd have to copy the error into Google/Bing and look for a solution, sorry.

[@JJ_TheGreat]

7:19 Why calculated columns and not measures??

[@MyOnlineTrainingHub]

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.

[@brettburton1477]

Recent subscriber, big fan

[@MyOnlineTrainingHub]

Awesome! Welcome 👋

[@ddp2049]

This was great now I understand kpi.

[@MyOnlineTrainingHub]

Have fun with them!

[@karwaisung2420]

Hi, I have added a new measure, but I dont know why the filters can’t work

[@MyOnlineTrainingHub]

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

[@margaretmicherda6136]

Does it work in excel 2019?

[@MyOnlineTrainingHub]

Yep 😊

[@DJ-pn9te]

why not use vba and use SQL queries to fetch the data?

[@MyOnlineTrainingHub]

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: kzbin.info/www/bejne/hJPLlWN4atGXgKc

[@bbqworld2103]

Good info thanks

[@MyOnlineTrainingHub]

Glad you found it helpful.

[@kenstav1]

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

[@MyOnlineTrainingHub]

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.

[@kenstav1]

@@MyOnlineTrainingHub this works when importing a single file, but does not seem to work when using and combining files from a folder (import Folder)

[@MyOnlineTrainingHub]

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.

[@rustynail8692]

Thanks for sharing 👍

[@MyOnlineTrainingHub]

Thanks for watching 😁

[@akin242002]

Excel with Power Pivot and Power BI = Modern day data analysis sage!

[@MyOnlineTrainingHub]

😁

[@IsMirDochLattens]

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.

[@MyOnlineTrainingHub]

Yeah, I still use regular PivotTables for basic stuff.

[@jrat104]

I guess if they are getting rid of Access is is good to put the code somewhere.

[@MyOnlineTrainingHub]

I wouldn't say they're getting rid of Access, it's just not being developed much anymore.

[@antoines.7682]

What about power query then?

[@EduTaxTuber]

Yes But, If there is no unique values ( or in unique contains duplicates) it is better to go with pivot table

[@MyOnlineTrainingHub]

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.

[@ignatiussilveira4257]

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.

[@MyOnlineTrainingHub]

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.

[@reyt7349]

❤

[@MyOnlineTrainingHub]

🙏

[@gino0612]

Very similar to Power BI. Except the power of the relationships it seems.

[@MyOnlineTrainingHub]

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.

[@johank4361]

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.

[@MyOnlineTrainingHub]

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

[@rob4433]

6:33 - What!?!?!?!? No one in the history of ever has had this problem????? ;)

[@MyOnlineTrainingHub]

😂

[@DimasFajar-ns4vb]

peace be upon you from me and zamzam water

[@maxhuber4924]

Too bad this is not available in Excel for Mac ...

[@MyOnlineTrainingHub]

If you use Excel a lot, Power Pivot and Power Query are worth having a PC for.

[@_DeepGroove_]

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)

[@VPutin-y7j]

People still use Excel?

[@perkristoffersson4153]

"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.

[@MyOnlineTrainingHub]

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.

[@perkristoffersson4153]

@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.

[@hagenfieberkorn5111]

Pivot tables can already be linked to multiple data sets in the same way without using power pivot. 🤷

[@MyOnlineTrainingHub]

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.

[@JJ_TheGreat]

12:17 So, you are forcing your using to scroll through that entire list of directors… I don’t think this is good design.

[@MyOnlineTrainingHub]

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. 😁

[@ericssontest2332]

,,,,,

[@jasonsmith1273]

Joke