Why EVERY Excel User Needs Power Query & Power Pivot

[@TexasGunDude]

Learning Power Query (and some Python) enabled me to change career track from a Technician to a Business Analysist.

[@MyOnlineTrainingHub]

Great to hear, Phillip!

[@atiqkhan9637]

can you share about what courses did you take? what did you learn in python?

[@TexasGunDude]

@@atiqkhan9637 I took 3 specializations from Coursera. Currently working on a Django class.

[@frederickzoreta6617]

Congratulations Phillip! I can totally feel you! Though I'm more into R/excel/PBI than python.

[@TexasGunDude]

@@frederickzoreta6617 I'm doing a lot of work in Tableau. Got to take some R classes soon.

[@santoshadhikari9933]

I watched this video a year ago and a year ago before that when I was in university. Now in 2023 when I have completed working for a year, this video now makes a lot more sense than it did in previous years. Its actually a very helpful concept explained in this video.

[@MyOnlineTrainingHub]

That's wonderful to hear! Congratulations on your progress 😊

[@tmb8807]

I discovered Power Query completely by accident after a colleague moved on without leaving any notes on how his various reports functioned, and I was asked to pick up where he left off… I’m flabbergasted that something so powerful yet so intuitive was sitting right under my nose. I shudder at the thought of how many hours I’ve wasted manually transforming data from different sources over the years. I’m tempted to say it’s life-changing.

[@MyOnlineTrainingHub]

Power Query is life changing 😊 Spread the word about it.

[@jawadahmadehssan6251]

You are the great Excel tutor. Thanks for all your videos.

[@MyOnlineTrainingHub]

So nice of you, Jawad!

[@ricos1497]

I find power pivot a very clunky tool, but very functional! I wish they'd assimilated it within power query, which looks and feels a little nicer (although I do like the fact that you don't have to save and close). To be honest, I'd love it if power query and pivot had exactly the same interface as power bi, which is nicer to work in.

[@MyOnlineTrainingHub]

Yes, the interface in Excel isn't as streamlined as Power BI, but I'm confident it will get there. The main thing is it still works and can reap huge efficiency gains.

[@zzzzzzzzzzzzzzzz9]

I never actually go into power piviot to create formula or measures

[@Dierken]

Mynda, You are great!

[@MyOnlineTrainingHub]

Very kind, Jupiter!

[@carolinavisnadi]

I will love to see a video discussing when the relational approach doesn't work and we actually need to merge datasets with PowerQuery (like old vlookups). And how to avoid not becoming huge and heavy? Alternatively, how to pivot tables in power query so we can have a 1 to many relationship, therefore be able to use snowflake schema as recommendeded. Thank you!

[@MyOnlineTrainingHub]

The relational approach can always work. You may need to set up dimension tables to facilitate the relationships, but this should be more efficient than merging the datasets. Power Query isn't where you create PivotTables, so I'm not sure what you're referring to when your second question. If you have follow-up questions, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@chrism9037]

Very helpful, thanks Mynda!

[@MyOnlineTrainingHub]

Thanks so much, Chris!

[@leksrebaula6139]

All your tutorials helped me a lot with my work. :)

[@MyOnlineTrainingHub]

Wonderful to hear 🙏

[@sontranbich9257]

It is true. I build a query to transform account data for my wife. That help her to save at least 1.5 - 2 hr/day collecting data then copy/paste & then update formula.

[@MyOnlineTrainingHub]

Wow, awesome to hear!

[@sarishvilangat]

I have seen a couple of videos of yours in the last few months, specially when I am stuck with complex financial modelling / workarounds and when I am in dearth of creatives for dashboards. I must say that you are simply awesome Mynda !!

[@MyOnlineTrainingHub]

Wow, thanks for your kind words! I'm delighted you found my videos helpful :-)

[@dirk.no-whisky.4u]

That's a dashing T-shirt. X-cellent choice, I say 😊😊

[@MyOnlineTrainingHub]

😁 thank you!

[@realpulsecoin]

Great content, greetings from Norway

[@MyOnlineTrainingHub]

Thanks for watching, Rasmus!

[@akulanarayana5284]

Very Very Good and Nice Presentation 🎉. 👍👌

[@MyOnlineTrainingHub]

Thank you so much 🙂

[@MohammadAshooryan]

Thanks for sharing your experience.

[@MyOnlineTrainingHub]

My pleasure!

[@richardayllon65]

Great and clarifying explanation

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@specialk7298]

Thank you. Should you learn Pivot Tables first, before learning Power Pivot?

[@MyOnlineTrainingHub]

Yes, ideally. If you want to get your PivotTable skills up to speed quickly, please consider my PivotTable Quick Start course: www.myonlinetraininghub.com/excel-pivottable-course-quick-start

[@ali4056]

Great video. Thank you.

[@MyOnlineTrainingHub]

Glad you liked it, Ali!

[@nikjojo]

very clear, thanks

[@MyOnlineTrainingHub]

Great to hear!

[@miladriasati]

You're such an expert in Excel. Extremely helpful! Thank you.

[@MyOnlineTrainingHub]

Glad I can help, Milad!

[@sebastiencrepel5032]

Thanks ! It was very clear and very helpful.

[@MyOnlineTrainingHub]

Glad to hear that! 🙏

[@PHWiest]

love all the recent comparison videos😀

[@MyOnlineTrainingHub]

Great to hear, Philip!

[@mahmudulchowdhury3824]

You are awesome, your content selection is very innovative and magical. Thanks from Australia

[@MyOnlineTrainingHub]

So nice of you!

[@ruchikapg]

Wow...you explained it very nicely

[@MyOnlineTrainingHub]

Thanks so much 😊

[@EyiBillion]

Brilliant work. Establishing the true difference. Greetings from Nigeria

[@MyOnlineTrainingHub]

Thank you very much!

[@DiamondRock2]

Verry helpfull and thank you very much Mayanda

[@MyOnlineTrainingHub]

My pleasure!

[@theoffsideartist]

Thanks for this video 🙏🏻

[@MyOnlineTrainingHub]

My pleasure 😊

[@timetoroll4441]

Great video thanks Mynda!

[@MyOnlineTrainingHub]

Cheers, Tim!

[@pakawatsasananan4099]

Would you recommend using Power Query or DAX measurement when calculate sales amount. For example, data only has sales quantity and unit price, and you want to see Sales amount as well.

[@MyOnlineTrainingHub]

I would do this in power query.

[@RossMaynardProcessExcellence]

Superb introduction. Thank you

[@MyOnlineTrainingHub]

Glad you liked it!

[@GeorgeAntonyEsq]

Understanding the concept is so crucial and this 6 minute video is easily the best...I normally just go to your videos, but today I went on another 1 hour video..while it was helpful, there was no conceptual explanation....Your style is the best...All the best and thanks

[@MyOnlineTrainingHub]

Wow, thanks for your kind words, George!

[@jessiesi1412]

Thank you. You did an excellent job. I learned a lot.

[@MyOnlineTrainingHub]

Glad to hear it!

[@gabrielprandini2629]

Thank you for the content!

[@MyOnlineTrainingHub]

My pleasure 😊

[@abdulbasital-sufyani6828]

Super useful... thanks

[@MyOnlineTrainingHub]

Great to hear 🙏

[@ps8463]

You teach wonderful

[@MyOnlineTrainingHub]

So nice of you 😊

[@williamlinares6848]

Thanks, excellent explication.

[@MyOnlineTrainingHub]

Glad it was helpful, William!

[@wayneedmondson1065]

Awesome Mynda! Not a day goes by that I don't find something new and useful that can be done with Power Query and Power Pivot. Just like Enrique, I've taken tasks previously consuming hours down to completion in minutes. Also, I've automated things that I previously considered possible only using VBA. So, I'm a fan and highly recommend learning these tools to anyone interested in unlocking new potential and finding new ways to get work done more quickly and more efficiently. As always, thanks for sharing all your great resources and insights :)) Thumbs up!!

[@MyOnlineTrainingHub]

Fantastic to hear, Wayne! Fingers crossed we can spread the word to more Excel users who can benefit from these efficiency gains.

[@leeblack2103]

@@MyOnlineTrainingHub i have data in a csv that exceeds the 32k cell limit when imported. Any recommendations on tool(s) that can help me with that situation?

[@glenomary]

Great explanation. Thank you. My attention was being refocused on the green journal behind you with the excel logo. Where can I find one? Thanks again

[@MyOnlineTrainingHub]

Thanks, Glen! The green Excel journal was a gift to us MVPs from Microsoft. I'm not aware of them being available to purchase.

[@ngee4925]

Can’t wait to watch your other videos on these subjects now!

[@MyOnlineTrainingHub]

Great to hear 😊

[@tabc6870]

I cannot count the number of times Mynda has saved my career.

[@MyOnlineTrainingHub]

Aw, that's amazing to hear 😊 thank you so much!

[@cherianiype]

Awesome stuff as Always Mynda! Thx much for presenting an important topic and breaking it down in simple English. Big fan of your work. Best wishes. Take care and stay safe. Cherian in Abu Dhabi

[@MyOnlineTrainingHub]

Thanks for your kind words, Cherian!

[@Flou-Slayer]

Thank you for your video, very interesting 👍 But this week I bought a MAC and install Office 2021 I was still on Windows and I'm surprised about Power Pivot and Query on Excel, I can't use it I'm looking for solutions but I don't found a tutorial or forum that could help me to counter this problem, apparently, with Extern SQL data we can do something but I don't know how the process? If someone has a solution? Or already meet this problem? Thank you in advance, 😄

[@MyOnlineTrainingHub]

You have two options, if you just want Power Query, then you'll be pleased to know that as of this week you can now run Power Query on Mac with a Microsoft 365 license. If you want all the other 'Power' tools, then you'll need to install Windows on Parallels or equivalent Virtual Machine software, and install the Windows version of Office.

[@shrikantbadge3978]

Merci Beaucoup!

[@MyOnlineTrainingHub]

My pleasure 😊

[@RAEN74]

thank you for finally giving me a resource to explain this!!

[@MyOnlineTrainingHub]

You're most welcome!

[@nouraali9391]

Thanks

[@MyOnlineTrainingHub]

You're welcome :-)

[@eduardobolivarvega6595]

muchas gracias!!! me ayudo

[@MyOnlineTrainingHub]

My pleasure 😊

[@RajendraPatel.]

Awesome explanation 😄 great 😊🙏

[@MyOnlineTrainingHub]

Glad you liked it, Raj!

[@kennygee2715]

PowerQuery is not available for the Home and Student edition of Office 2010, only the pro versions.

[@MyOnlineTrainingHub]

From memory, in 2010 Power Query is an add-in. However, as 2010 is no longer supported, you may not be able to get it anymore.

[@chubbydawme]

THANK YOU!

[@MyOnlineTrainingHub]

You're welcome!

[@alejandrozamora7919]

Very clear! Thanks for sharing!

[@MyOnlineTrainingHub]

You're very welcome!

[@shakthiatukorala595]

Nice tutorial...

[@MyOnlineTrainingHub]

Many thanks!

[@daveh4208]

When I try to perform power query functions, many are 'grayed' out and not accessible, even though I watch you preform the function in a video. Are there settings that cause this that I need to change, or do you have to pay extra for the full power query capability?

[@MyOnlineTrainingHub]

If a feature is grayed out it will be because the data/column you have selected isn't supported for that feature. e.g. if you have a column with the data type of Text selected and you try to use the Date tools, they won't be available until you change the data type of the column to 'Date'.

[@enerxiazelementa]

can these all used online and accessible for other users?

[@MyOnlineTrainingHub]

Not really. Once Power Query loads the data to the Power Pivot data model and you build reports on it, can be consumed online, but not refreshed.

[@knightrdrx]

if the company has their data stored in SQL server i'm assuming you'd be writing SQL queries first? and then put into excel for further analysis and reporting. every analyst job ad says analyze but i'm not really sure how to prepare for that as each job has their own method and excel formulas used or already in place. i think it just means bringing insight and answering questions to raw data whether it's claims data or product sales?

[@MyOnlineTrainingHub]

Nope, you can use Power Query to connect to the SQL server and it will write the queries for you. This is important to enable Power Query to push the processing of the queries back to the SQL server. If you write your own queries then you're making your PC do that processing and this results in slower refreshes. It's called Query Folding, if you want to look into it further.

[@harryakb11]

Thank you

[@MyOnlineTrainingHub]

You're welcome!

[@wildcat0871]

Very concise ed informative, great video. Thanks a lot.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@Giovanni-vx8xl]

great video as always . Thanks Mynda.

[@MyOnlineTrainingHub]

Glad you enjoyed it, Giovanni!

[@LizMAlex]

Great explanation on the distinction between both!

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@mrdiv8418]

Thank you! Very informative overview!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@darrylmorgan]

Hi Mynda!Great Advice On These Awesome Tools...Thank You :)

[@MyOnlineTrainingHub]

Cheers, Darryl!

[@TheTinaMarbury]

Could you do a video on using Powery Query with Power pivot?

[@MyOnlineTrainingHub]

Here's a video on building a dashboard using Power Query and Power Pivot: kzbin.info/www/bejne/inyTdH14ltappLs

[@doughminawfal8003]

Thank you very much, I still have some difficults but I am sure that with your videos I can overcome these obstacles

[@MyOnlineTrainingHub]

Great to see you're giving it a go!

[@Maldive177]

Hi there, this is amazing. I do have a question. i have already loaded 9000 records into the power pivot and built a dashboard based on it, it is a monthly report. but now problem the is, each month i have new sales data. in that case, how to just load or add the data to the table that 's already in the power pivot??? cause i tried many ways, it would just add the data to a different tab, like a new table, but that is not how i want it. please help. thanks in advanced.

[@MyOnlineTrainingHub]

There's no way to change the load source of an existing table in Power Pivot. You'll have to load it as a new table and then edit the model to use this table instead of the original table.

[@AEB7791]

Nice T-shirt!)

[@MyOnlineTrainingHub]

Thanks! 😄

[@vanhercol752]

Hello, about BI should i learn power bi or sql first ?

[@MyOnlineTrainingHub]

I suppose that depends on what type of BI job you want. Learning SQL will be a good foundation for learning DAX, but then so is Excel. Maybe a survey of job ads will give you an indication of which skill is more in demand.

[@vanhercol752]

@@MyOnlineTrainingHub Analyst Business intelligence

[@markusschoch6754]

Hi, I am new to all this. I have watched a bunch of your videos and while they are absolutely great, I cannot find the answer to my problem. Perhaps you can guide me. I have a list in Power Query of daily data since 2007 with each a value. Let's say it s about the stock market. So daily dates since 2007 and in the second column is the value of the stock market. Now I want to get rid of all the rows (dates) except the last day of each month. Is there a solution?

[@MyOnlineTrainingHub]

I'd create another table that lists the last day of each month, and then merge the two tables together to bring in the value for each month end. If you need help with it, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@arundev1]

If you see my message, just want to know if there is an option for reversing the COUNTIFS. Like the data already counted need to make it into the list again.

[@MyOnlineTrainingHub]

Hi Dev, maybe... you can expand the number of rows based on the count value.

[@denniskarani9961]

Get me started in power query and power pivot, How long will it take me?

[@MyOnlineTrainingHub]

I'd estimate 3-4 times the course length and then ongoing practice: www.myonlinetraininghub.com/courses

[@jg0943]

These tools are game changers for productivity.

[@MyOnlineTrainingHub]

Indeed!

[@drillcream]

Does MS Power BI make Power Query / Pivot in Excel redundant?

[@MyOnlineTrainingHub]

That depends. This video explains the pros and cons of using Excel vs Power BI: kzbin.info/www/bejne/Zp-kgmqdhbSSbLs

[@drillcream]

@@MyOnlineTrainingHub awesome, thank you!

[@clairezhang7351]

Thanks for the video. It is very helpful ☺

[@MyOnlineTrainingHub]

You're welcome 😊

[@alexandreguerra6437]

Excellent as always!

[@MyOnlineTrainingHub]

Thanks so much 😊

[@nakiasimmons708]

Every Quarter I run Supplier Diversity reports and I have to use reports submitted by D&B and merge with spend data from SAP. The information in the report from D&B is so complicated and I used Power Query and Power Pivot to clean and analyze the report. It used to take me over 5 hours over several days now I get updated data within seconds.

[@MyOnlineTrainingHub]

Wow, that's awesome to hear, Nakia!

[@SimpleExcelVBA]

Awesome explanation, thank You :)

[@MyOnlineTrainingHub]

Glad it was helpful!

[@denniskarani9961]

I need to learn power query and power pivot

[@MyOnlineTrainingHub]

Awesoem to hear. You can take my courses here: www.myonlinetraininghub.com/courses

[@ahmedshakebzubairi25]

Very well explained. This encouraged me to subscribe to your channel. Thanks

[@MyOnlineTrainingHub]

Thank you and welcome, Ahmed!

[@shakiraasfoor7599]

Thanks for your efforts, all your videos are useful

[@MyOnlineTrainingHub]

Glad you like them, Shakira!

[@teenadaniel2339]

Mam... In power query,how can we filter certain things from master data to multiple sheets

[@MyOnlineTrainingHub]

Power Query isn't designed to split data into multiple sheets, however you can use PivotTables to automate that, as explained here: www.myonlinetraininghub.com/excel-pivot-tables-to-extract-data

[@teenadaniel2339]

@@MyOnlineTrainingHub thanks for your reply...is it possible using an excel macro to do the same..

[@MyOnlineTrainingHub]

Yes, if you prefer.

[@teenadaniel2339]

@@MyOnlineTrainingHub can you please show thorough your video how the script is performed

[@vivivioleta9260]

Hi, tks for this video, I have problem. I'm using ms office 2019, when I try to open power pivot, there's pop up message: unable to cst COM object of type 'Microsoft.Office.Inerop.Excel.WorkbookClass' to interface type 'Microsoft.Office.Interop.Excel_Workbook'. This operation failed because the Queryinterface call on the COM component for the interface with IID '{000208DA-0000-0000-C00-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)). what should I do? Tks.

[@MyOnlineTrainingHub]

Yikes! I'm not sure Vivi. Maybe try repairing Excel.

[@dougmphilly]

question : if creating calculated columns is more efficient in power query, but power pivot files are much smaller than files based pivot tables, is that efficiency negated?

[@MyOnlineTrainingHub]

It's less work for Excel to calculate the column once on data load in Power Query than it is to repeatedly calculated in the Power Pivot data model. In a small file, it's probably negligible either way.

[@dougmphilly]

@@MyOnlineTrainingHub thank you for response.

[@jsasakituck1998]

Can you please do a comparison video between (power query + power pivot) and (power query + pivot table) what is the difference between power pivot's relationship and power query's merge? What can DAX do that calculated items/field not do in pivot table?... etc.

[@MyOnlineTrainingHub]

Power Query and PivotTables is the same as comparing Power Query and Power Pivot. PQ Merge brings data into a single table, whereas Power Pivot uses relationships to do this on the fly in a PivotTable, which should in theory make for a smaller and more efficient file.

[@jsasakituck1998]

Thank you so much! Can you elaborate on "Pivot Table Calculation limitations that DAX can overcome"?

[@zzzzzzzzzzzzzzzz9]

I use PP and PQ daily, no matter how big, small or complex yhe data is. I just love using both tools and fir a nerd like me, they're perfect.

[@MyOnlineTrainingHub]

Great to hear!

[@zzzzzzzzzzzzzzzz9]

@@MyOnlineTrainingHub typo's galore. Hate typing on my phone 😀

[@BrokenRecord-i7q]

Seems like pivot is not available for mac

[@MyOnlineTrainingHub]

Correct, Power Pivot is not yet available on a Mac, sorry.

[@chineando8078]

After learning both and using at work these tools , i realized some of the people in my organization that i need to share the files with, use Mac and cannot use my pivot tables made with power pivot as it does not support it .

[@MyOnlineTrainingHub]

Yep, Mac is a long way from supporting Power Pivot. Perhaps your co-workers can use Parallels and install Windows OS with Excel on there so they can work with your files.

[@Anonymous-ho1mt]

2:30 oh man, you broke the first rule. Colleagues like this are a pain in the A$$.

[@arturocarrillo1553]

Good topic but poor video recording. Very blurry

[@MyOnlineTrainingHub]

It's available in 1080p. You can change it in the settings via the cog icon in the bottom right.

[@iamthemoss]

I really think power query can replace power pivot

[@MyOnlineTrainingHub]

How so? Power Query can't write dynamic formulas that update when you change the PivotTable like you can with DAX.

[@iamthemoss]

@@MyOnlineTrainingHub My boss is a DAX animal, he writes calculated fields in Power Query using the advanced editor. We work with very large data sets. Our pivots are very static as the data is financial in nature. Great channel.

[@MyOnlineTrainingHub]

👍Ah, calculated columns are perfect for PQ, but DAX measures can't be replicated. Sounds like you don't need them though, which is a bonus!

[@JeremyCooperIsAwesomeSauce]

NEVER SHOW YOUR BOSS!!!! Do your 30 minute cleanup and use those extra 5.5 hours to do something NOT work related ;-) jkjkjkjkjk

[@MyOnlineTrainingHub]

Always an option 😉

[@stephenl5693]

Yup I guess it depends on how well your boss treats you. I had a good boss and was valued, trusted and rewarded well for using power query cleaning data much quicker. Then now I work for a different company not so rewarding boss who just wants to squeeze employees far beyond to get the work done, sooooo secretly power query the data and relax a few hours of time utilizing further automation research 😅

[@vasgen4360]

Absolute zero info

[@ahmedshalaby9343]

thanks

[@MyOnlineTrainingHub]

You're welcome! 😊