Power Query vs Power Pivot

one of the most common questions i get is what's the difference between power query and powerpivot and when should i use them in this video i'm going to demystify these tools and help you identify whether it's worth your time to learn them and which one will give you the biggest benefit in the simplest of terms power query is all about automating the task of getting and cleaning data and powerpivot is all about analyzing data using the familiar pivot table tool every excel user should know the nice thing about learning these tools is that they're available in both excel and power bi in fact if you're planning to learn power bi then you must know power query and power pivot because these tools are integral to how power bi works power query is going to help you if you regularly get data from multiple sources like excel files text files external databases like sql and access or even the web which you then consolidate into one table so you can analyze it or if your data is spread over multiple excel worksheets or workbooks and you spend time bringing it together into one table or if your data is messy and you spend a lot of time cleaning and transforming it before you can use it in your analysis and reports for example splitting text into multiple columns removing columns rows or duplicates or maybe you need to add calculated columns by the way adding calculated columns in power query is typically more efficient than adding them in powerpivot so keep this in mind if you're a powerpivot user or maybe you clean or trim text or remove empty spaces or characters or any other repetitive data cleaning tasks like unpivoting or merging data that waste your time and makes you feel like this so if you perform these data gathering and cleaning tasks each time you receive new or updated data or you currently use vba to run the data cleaning tasks then power query will transform your excel world and i'm not exaggerating i regularly get messages from people telling me how power query has saved them hours of work every week like this one from enrique said last week i spent six hours cleaning and putting two large data sets together today with power query it took me about 30 minutes to come to the same result i obviously showed it to my boss and he was stunned i'm not surprised enrique that's a huge saving so if power query is all about automating the boring tasks of getting and cleaning data to save time powerpivot on the other hand is all about analyzing the data and we do this with pivot tables and charts but unlike regular pivot tables and charts with powerpivot you can also write measures using the dax formula language which you can see is very similar to excel functions dax measures enable you to get around the limitations of calculated fields and calculated items that we have available in regular pivot tables another significant difference is powerpivot's ability to work with data spread over multiple tables through relationships much like an access database or other relational database this enables you to analyze data from multiple tables in a pivot table and there's no need to flatten the data into a single table with vlookup or index and match anymore and unlike excel powerpivot isn't limited by the number of rows in the worksheet the data is stored in the powerpivot model which means you can work with millions of rows of data that get compressed using a special algorithm that's more efficient than traditional excel resulting in smaller and faster files so to recap power query is all about automating the task of getting and cleaning data from there you can load the data direct to the powerpivot model or to a table in the excel grid whereas powerpivot is all about analyzing the data enabling you to write measures with dax work with multiple tables of data and millions of rows and while these tools were designed to work together it doesn't mean you can't use them exclusively for example if a lot of your time is spent on the boring data gathering and cleaning tasks then power query is going to be a life saver and if you have problems with slow workbooks that can't handle the size of your data or the number of formulas you have then powerpivot can help you and of course if you're planning on using power bi then learning these tools is going to give you a huge head start now i'm sure you're eager to get your hands on these amazing tools so where do you find them well it depends on which version of excel you have power query is available in all versions from excel 2010 onward there are some version limitations and in some versions of excel it lost the power query name and was called get and transform powerpivot has had a bumpy ride over the years and is only available in some versions of excel 2013 and 2016 but after lobbying from us mvps from excel 2019 onward powerpivot is available in all versions again now i've included links in the video description with a complete list of versions supporting power query and powerpivot so you can find out more for your specific version of excel now if you'd like to learn these tools and get up to speed quickly i've included links in the video description to my courses i hope you found this video helpful don't forget to check out the links in the video description for more information and if you like this video please give it the thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 35,409

Rating: 4.9911571 out of 5

Keywords: power query, power pivot, power query vs power pivot

Id: CLClvAGr0EY

Length: 6min 3sec (363 seconds)

Published: Tue Apr 13 2021