Power Query and Power Pivot Definitive Guide

One of the most common questions I get is, what’s the difference between Power Query and Power Pivot and when should I use them.

In this post I’m going to demystify these Excel tools and help you identify whether it’s worth your time to learn them and which one will give you the biggest benefit.

Watch the Video

Power Query vs Power Pivot : Download Infographic

Index

Power Query

• What is Power Query for
• Versions of Excel Compatible with Power Query
• Get Power Query
• Power Query course



Power Pivot

• What is Power Pivot for
• Versions of Excel Compatible with Power Pivot
• Find Power Pivot
• Backward Compatibility for Power Pivot Files
• Power Pivot course



Other

• Power Query vs Power Pivot
• Help, I don’t have a compatible version of Excel!

What is Power Query for

Power Query is all about automating the task of getting and cleaning data so you can then use that data in PivotTables, formulas, Power Pivot and more.

If you can relate to any of these then statements, then Power Query will transform your Excel world.

1. I regularly get data from multiple sources e.g. Excel files, CSV/Text files, external databases (SQL, Access etc.), web and I have to bring it into Excel and consolidate it into one table so I can analyse it
2. My data is spread over multiple Excel worksheets/workbooks and I have to consolidate it into one table so I can use it
3. The data I receive requires cleaning before I can use it e.g.:
   • splitting text into multiple columns
   • remove columns/rows/duplicates
   • adding columns
   • clean/trim text or remove empty spaces or characters I don’t need
   • Pivoting/unpivoting
   • Headers are spread over multiple rows
   • Fill blank cells
   • Format text as dates or numbers
   • Other random data cleaning tasks that drive me crazy and waste my time
4. I have to perform the above data getting and cleaning activities each time I receive the new/updated data
5. I currently use VBA to run the data cleaning tasks

While Excel can do all of the above tasks the difference with Power Query is that you can automate those tasks.

So next time you simply refresh Power Query and it goes and gets your data and cleans it for you in seconds.

Much like you might use VBA to automate repetitive tasks like those above.

However, unlike VBA, Power Query doesn’t require you to learn a complicated programming language to use it. You can be up and running with Power Query fast.

Plus it can connect to a huge range of data sources [Fig1.] (and more are being added all the time):

Back to Index

Versions of Excel Compatible with Power Query

Power Query is available in Excel 2010 onwards, so there's no excuse for not using it.

Power Query is still in development for the Mac and Excel Online.

Click here for the complete list of versions of Excel supporting Power Query.

Back to Index

Find Power Query

If you have Excel 2016 or later then you'll find Power Query on the Data tab in the Get & Transform group [Fig2.]:

Back to Index

Power Query Course

Power Query is easy to use, but not everything you'll want to do is self explanatory. That's where some training will get you started on the right foot and up and running fast.

If you’d like to learn Power Query in 6 hours, consider taking my Power Query course.

Back to Index

What is Power Pivot for

Power Pivot is all about analysing data using the familiar PivotTable tool every Excel user should know (if you haven’t mastered regular PivotTables yet, then you can learn them here).

The difference between regular PivotTables and Power Pivot PivotTables is more “power”, of course 🙂

Power Pivot will be a huge asset to your Excel arsenal if you can relate to any of these statements:

1. My Excel files are big and slow. Excel just can’t cope with the amount of data I have
2. My data is so big it exceeds the 1,048,576 rows available in a worksheet
3. My data is in external databases and I have to wait for my IT guy/gal to write queries for me just so I can get at the data and start analysing it
4. My Data is spread over multiple tables and I have to use formulas like VLOOKUP/XLOOKUP and INDEX & MATCH to bring it together into one table so I can analyse it in PivotTables or formulas
5. PivotTable Calculated Fields and Items can’t do the calculations I need so I have to write my own formulas outside of the PivotTables

Unlike regular Excel PivotTables, Power Pivot can store multiple tables of data in the one model.

You simply create relationships between those tables like you would with Access or other relational databases [Fig3.].

These relationships enable you to create PivotTables that mash up the data from all or any of the tables.

You never have to write another VLOOKUP or INDEX & MATCH formula again.

Power Pivot also comes with a new formula language (DAX – Data Analysis Expressions) that is very similar to the Excel formula language [Fig4.]:

DAX enables you to write sophisticated calculations that far exceed the capabilities of the regular PivotTable calculated fields and items you may already be familiar with.

Unlike regular PivotTables, Power Pivot can handle big data and it’s fast. I’m talking millions of rows of data.

Power Pivot has a new compression algorithm that is more efficient than that of Excel or the Pivot Cache when it comes time to compressing data spread over multiple tables.

Think a star scheme table structure where there are multiple columns in the file which contain duplicates.

You can connect Power Pivot direct to a huge range of data sources including relational databases, data feeds, Reporting Services, text files, multidimensional sources, cloud services, data feeds, Excel files, and data from the Web, into a single Excel workbook.

You can also connect to a Power Query query, which means you have access to all of the Power Query data sources, too.

And more importantly, you’re not reliant on your IT department to get you the data.

Obviously you'll have to get their permission and possibly the password to connect to the database. Tip: a box of donuts for the IT team might help.

Back to Index

Versions of Excel Compatible with Power Pivot

Power Pivot is available in Excel 2010 or Excel 2013/2016 Office Professional Plus, in the standalone edition of Excel 2013/2016 and all versions of Excel 2019 onward and Office 365.

Power Pivot is not available for the Mac and has limited functionality in Excel Online.

Click here for the complete list of versions of Excel supporting Power Pivot.

Back to Index

Get Power Pivot

Power Pivot is available in all versions of Excel 2019 onward. Earlier versions of Excel are less fortunate.

Tip: Power Pivot is available for 32-bit Excel and 64-bit. If you want to work with big data then you need 64-bit Excel.

Older versions of Excel:

For Excel 2010 Professional Plus/ProPlus users you can download the free Power Pivot add-in here from Microsoft.

For Excel 2013 and 2016, if your version of Excel comes with Power Pivot then it will already be installed, you just need to enable it.

To check if your Excel 2013 or 2016 version has Power Pivot go to the File tab > Options > Add-ins > In the ‘Manage’ drop down list choose ‘COM Add-ins’ > click Go.

If ‘Microsoft Office PowerPivot for Excel 2013’ is in the list you’re good to go.

Note: If you have the Standalone Excel 2013 version below 1511, you can simply update your version and Power Pivot will be available.

Back to Index

Help, I don’t have a compatible version of Excel!

If you don’t have a compatible version of Excel for Power Query and Power Pivot then I highly recommend getting Microsoft 365 (I don't make any money for this recommendation).

With 365 you get the latest updates, which are released every month. It's well worth the investment.

Back to Index

Backward Compatibility for Power Pivot Files

If you share a Power Pivot file with someone who doesn’t have Power Pivot they will be able to view/read the file but they can’t interact with the Slicers, change the PivotTables or refresh the data.

Use the table below [Fig5.] to identify the compatibility options available to you and your users.

Back to Index

Power Pivot course

Power Pivot is no more complicated to learn than Excel, in fact it's much easier, but there are some rules you must follow to ensure you set your models up correctly.

Plus, while the formula language structure is very similar to that of Excel, there are new functions to learn and you need to understand how changes to the PivotTable structure will impact the formulas.

If you’d like to be up and running with Power Pivot in just over 5 hours, consider taking my Power Pivot course.

Back to Index

Power Query and Power Pivot

These tools work brilliantly together and if you're familiar with them then you'll know there are some overlapping capabilities, which means you might wonder when to use Power Query vs Power Pivot. Here are some guidelines:

• Get and clean data with Power Query and load it into Power Pivot. Unless you have Excel 2010, which means this route requires a hack (I cover that in my course).
• Use Power Pivot to model your data, leveraging the relationship capabilities. That means don't flatten your data into one huge table.
• Create calculated columns in Power Query where row context is required. Use Power Pivot for DAX calculations that require filter context, time intelligence and or aggregation.

These are not hard and fast rules but they’re a good starting point for consideration.

Back to Index