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.
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.
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 !!
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
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
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.
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.
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.
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
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 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?
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, 😄
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.
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.
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?
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.
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.
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?
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
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?
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'.
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.
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.
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.
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.
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.
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.
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 .
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.
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
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?
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.
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 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.
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 😅
Learning Power Query (and some Python) enabled me to change career track from a Technician to a Business Analysist.
Great to hear, Phillip!
can you share about what courses did you take? what did you learn in python?
@@atiqkhan9637 I took 3 specializations from Coursera. Currently working on a Django class.
Congratulations Phillip! I can totally feel you! Though I'm more into R/excel/PBI than python.
@@frederickzoreta6617 I'm doing a lot of work in Tableau. Got to take some R classes soon.
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.
That's wonderful to hear! Congratulations on your progress 😊
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.
Power Query is life changing 😊 Spread the word about it.
You are the great Excel tutor. Thanks for all your videos.
So nice of you, Jawad!
That's a dashing T-shirt. X-cellent choice, I say 😊😊
😁 thank you!
You are awesome, your content selection is very innovative and magical. Thanks from Australia
So nice of you!
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 !!
Wow, thanks for your kind words! I'm delighted you found my videos helpful :-)
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
Wow, thanks for your kind words, George!
Very Very Good and Nice Presentation 🎉. 👍👌
Thank you so much 🙂
All your tutorials helped me a lot with my work. :)
Wonderful to hear 🙏
love all the recent comparison videos😀
Great to hear, Philip!
Thank you. Should you learn Pivot Tables first, before learning Power Pivot?
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
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.
I would do this in power query.
Thank you very much, I still have some difficults but I am sure that with your videos I can overcome these obstacles
Great to see you're giving it a go!
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.
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.
I never actually go into power piviot to create formula or measures
Thanks for sharing your experience.
My pleasure!
Brilliant work. Establishing the true difference. Greetings from Nigeria
Thank you very much!
thank you for finally giving me a resource to explain this!!
You're most welcome!
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
Thanks for your kind words, Cherian!
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.
Great to hear!
@@MyOnlineTrainingHub typo's galore. Hate typing on my phone 😀
Great content, greetings from Norway
Thanks for watching, Rasmus!
Can’t wait to watch your other videos on these subjects now!
Great to hear 😊
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
Thanks, Glen! The green Excel journal was a gift to us MVPs from Microsoft. I'm not aware of them being available to purchase.
These tools are game changers for productivity.
Indeed!
You're such an expert in Excel. Extremely helpful! Thank you.
Glad I can help, Milad!
Great and clarifying explanation
Glad you liked it 😊
Thank you. You did an excellent job. I learned a lot.
Glad to hear it!
very clear, thanks
Great to hear!
Thanks ! It was very clear and very helpful.
Glad to hear that! 🙏
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!!
Fantastic to hear, Wayne! Fingers crossed we can spread the word to more Excel users who can benefit from these efficiency gains.
@@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?
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, 😄
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.
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.
Wow, awesome to hear!
Very helpful, thanks Mynda!
Thanks so much, Chris!
Mynda, You are great!
Very kind, Jupiter!
Verry helpfull and thank you very much Mayanda
My pleasure!
Great video. Thank you.
Glad you liked it, Ali!
Wow...you explained it very nicely
Thanks so much 😊
Thanks for this video 🙏🏻
My pleasure 😊
Could you do a video on using Powery Query with Power pivot?
Here's a video on building a dashboard using Power Query and Power Pivot: th-cam.com/video/TK0CJBaqvnY/w-d-xo.html
Superb introduction. Thank you
Glad you liked it!
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?
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.
Super useful... thanks
Great to hear 🙏
PowerQuery is not available for the Home and Student edition of Office 2010, only the pro versions.
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.
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.
Wow, that's awesome to hear, Nakia!
Thank you for the content!
My pleasure 😊
Thanks
You're welcome :-)
Get me started in power query and power pivot, How long will it take me?
I'd estimate 3-4 times the course length and then ongoing practice: www.myonlinetraininghub.com/courses
You teach wonderful
So nice of you 😊
Hi Mynda!Great Advice On These Awesome Tools...Thank You :)
Cheers, Darryl!
Very concise ed informative, great video. Thanks a lot.
Glad you enjoyed it!
Great video thanks Mynda!
Cheers, Tim!
I cannot count the number of times Mynda has saved my career.
Aw, that's amazing to hear 😊 thank you so much!
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?
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
Awesome explanation 😄 great 😊🙏
Glad you liked it, Raj!
Thanks, excellent explication.
Glad it was helpful, William!
can these all used online and accessible for other users?
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.
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?
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'.
Great explanation on the distinction between both!
Glad you liked it 😊
I need to learn power query and power pivot
Awesoem to hear. You can take my courses here: www.myonlinetraininghub.com/courses
Thank you! Very informative overview!
Glad you enjoyed it!
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.
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.
muchas gracias!!! me ayudo
My pleasure 😊
great video as always . Thanks Mynda.
Glad you enjoyed it, Giovanni!
Very clear! Thanks for sharing!
You're very welcome!
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.
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.
Thank you so much!
Can you elaborate on "Pivot Table Calculation limitations that DAX can overcome"?
Hello, about BI should i learn power bi or sql first ?
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.
@@MyOnlineTrainingHub Analyst Business intelligence
Thank you
You're welcome!
THANK YOU!
You're welcome!
Very well explained. This encouraged me to subscribe to your channel. Thanks
Thank you and welcome, Ahmed!
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.
Hi Dev, maybe... you can expand the number of rows based on the count value.
Merci Beaucoup!
My pleasure 😊
Nice tutorial...
Many thanks!
Thanks for the video. It is very helpful ☺
You're welcome 😊
Does MS Power BI make Power Query / Pivot in Excel redundant?
That depends. This video explains the pros and cons of using Excel vs Power BI: th-cam.com/video/0nAQ7gPO_6Y/w-d-xo.html
@@MyOnlineTrainingHub awesome, thank you!
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 .
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.
Thanks for your efforts, all your videos are useful
Glad you like them, Shakira!
Mam... In power query,how can we filter certain things from master data to multiple sheets
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
@@MyOnlineTrainingHub thanks for your reply...is it possible using an excel macro to do the same..
Yes, if you prefer.
@@MyOnlineTrainingHub can you please show thorough your video how the script is performed
Awesome explanation, thank You :)
Glad it was helpful!
Nice T-shirt!)
Thanks! 😄
Excellent as always!
Thanks so much 😊
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?
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.
@@MyOnlineTrainingHub thank you for response.
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.
Yikes! I'm not sure Vivi. Maybe try repairing Excel.
Seems like pivot is not available for mac
Correct, Power Pivot is not yet available on a Mac, sorry.
I really think power query can replace power pivot
How so? Power Query can't write dynamic formulas that update when you change the PivotTable like you can with DAX.
@@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.
👍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!
Good topic but poor video recording.
Very blurry
It's available in 1080p. You can change it in the settings via the cog icon in the bottom right.
2:30 oh man, you broke the first rule. Colleagues like this are a pain in the A$$.
NEVER SHOW YOUR BOSS!!!! Do your 30 minute cleanup and use those extra 5.5 hours to do something NOT work related ;-) jkjkjkjkjk
Always an option 😉
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 😅
Absolute zero info
Thanks for video.
Do you have any video plans for Python in PBI?
Not at this point, but I’ll keep it in mind.
thanks
You're welcome! 😊