If you attended my Excel Dashboard webinar with Power Query and Power Pivot this past week, you’ll have seen how easy it is to build interactive dashboard reports with these amazing new tools.
If you missed it, you can catch a replay on the Excel webinars page. Scroll down to the second webinar which includes Power Query and Power Pivot.
It was great to see the excitement around Power Query and Power Pivot, but this meant there were lots of questions about these tools, so I’ll attempt to answer them here.
- What is Power Query for
- Versions of Excel Compatible with Power Query
- Get Power Query
- Backward Compatibility for Power Query Files
- Power Query course
- What is Power Pivot for
- Versions of Excel Compatible with Power Pivot
- Get Power Pivot
- Backward Compatibility for Power Pivot Files
- Power Pivot course
- 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.
- 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
- My data is spread over multiple Excel worksheets/workbooks and I have to consolidate it into one table so I can use it
- 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
- 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
- I have to perform the above data getting and cleaning activities each time I receive the new/updated data
- 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):
Versions of Excel Compatible with Power Query
Power Query is available in Excel 2010 onwards. Click here to check if your version of Excel is compatible.
Power Query is not available for the Mac.
Get Power Query
You can download the free Power Query add-in here from Microsoft. It’s available for 32-bit and 64-bit Excel.
Note: if you have Excel 2016 then you already have Power Query. You’ll find it on the Data tab in the Get & Transform group [Fig2.]:
Backward Compatibility for Power Query Files
So, you’ve seen how amazing Power Query is and you know it could change the way you work in Excel and save you hours of time each day/week/month, but what happens if you send an Excel file to someone who doesn’t have Power Query?
Remember Power Query is all about getting and cleaning your data so if you send an Excel file to someone who doesn’t have Power Query installed then they can see the data and work with it, but they can’t refresh the query to update the data.
In most cases this won’t matter since you will have already done that, or can do it for them when required, especially if the file containing the query is on a shared network drive. You simply open it, refresh, save and then they can see the latest data.
If they have Power Query, but it’s a different version to yours then they will receive a warning advising them that the query may not work as expected.
Bottom line: If you want others to be able to edit and refresh your query then you ideally need the same version of Power Query add-in installed (but you can have different versions of Excel) in order to work with the query as expected, otherwise you may get erroneous results.
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 less than 4 hours, consider taking my Power Query course.
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:
- My Excel files are big and slow. Excel just can’t cope with the amount of data I have
- My data is so big it exceeds the 1,048,576 rows available in a worksheet
- 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
- My Data is spread over multiple tables and I have to use formulas like VLOOKUP and INDEX & MATCH to bring it together into one table so I can analyse it in PivotTables or formulas
- 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 possible the password to connect to the database. Tip: a box of donuts for the IT team might help.
Versions of Excel Compatible with Power Pivot
Power Pivot is available in Excel 2010 or Excel 2013/2016 Office Professional Plus, Office 365 Professional Plus 2010/2013/2016, or in the standalone edition of Excel 2013/2016.
Power Pivot is not available for the Mac.
Get Power Pivot
For Excel 2010 Professional Plus/ProPlus users you can download the free Power Pivot add-in here from Microsoft.
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.
For Excel 2013 onwards, Power Pivot is already 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.
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 the easiest option is to purchase the standalone version of Excel 2013 or 2016. It’s usually around US$110.
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.
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 less than 6 hours, consider taking my Power Pivot course.
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.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.