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.
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
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
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.
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.
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 😅
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'.
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.
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.
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
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.
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.
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.
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
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.
@@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.
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.
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?
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 !!
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.
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.
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.
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.
