May 29, 2018.

In Excel 2016, Power Query isn’t an add-in — it’s a native feature of Excel, just like charts and pivot tables are native features. If you’re working with Excel 2016, you don’t have to install any additional components. You’ll find Power Query in Excel 2016 hidden on the Data tab, in the Get & Transform group.

If you’re working with Excel 2010 or Excel 2013, you need to explicitly download and install the Power Query add-in. As of this writing, the Power Query add-in is available to you only if you have one of these editions of Office or Excel:

• Office 2010 Professional Plus: Available for purchase through any retailer
• Office 2013 Professional Plus: Available through volume licensing only
• Office 365 Pro Plus: Available with an ongoing subscription to Office365.com
• Excel 2013 Stand-alone Edition: Available for purchase through any retailer

If you have any of these editions, you can install and activate the Power Query add-in. Simply enter the search term Excel Power Query add-in into your favorite search engine to find the free installation package.

Note that Microsoft offers Power Query for both Excel 2010 and Excel 2013 in both 32- and 64-bit platforms. Be sure to download the version that matches your version of Excel as well as the platform on which your PC is running.

After the add-in is installed, activate it by following these steps:

1. Open Excel and look for the Power Query command on the Insert tab. If you see it, the Power Query add-in is already activated. You can skip the remaining steps.
2. From the Excel Ribbon, choose File→Options.
3. Choose the Add-Ins option on the left, and then look for the Manage drop-down list at the bottom of the dialog box. Select COM Add-Ins and then click Go.
4. Look for Power Query for Excel in the list of available COM add-ins. Select the check box next to each one of these options and click OK.
5. Close and restart Excel.

A successful install results in a new Power Query tab on the Excel Ribbon.

Understanding the difference between the various Power BI tools can be very confusing for beginners.

What is the difference between Power Query vs Power Pivot? Or between Power Pivot vs Power BI? And when would you use one over the other?

In this article we will break down the different power tools individually, explain their roles and then understand how they can all work together.

What is Power Query?

Power Query is the tool used to import, clean and shape data in Excel.

If you've never heard of Power Query and want to get familiar with what it can do, then check out this quick intro video:

It first appeared in Excel 2013 but was made available only as an add-in named Power Query. From Excel 2016 and in Microsoft 365, it is available directly within Excel from the Data tab, but is labeled Get & Transform.

Power Query can import data from a variety of sources including CSV, text files, a folder on a drive, Facebook and Salesforce. New connections are being built into Power Query regularly making it easy to get data from external sources.

After importing data (connecting to a source) the Power Query Editor window opens.

This tool is extremely powerful, incredibly useful - but yet simple to use. It has a nice easy to use interface with a variety of commands that will be recognizable to many Excel users.

As users perform commands to clean and shape their data such as split columns, replace values or remove duplicates - Power Query records the step and writes M code behind the scenes (similar to how VBA is written when you record a macro).

M is the language of Power Query. It is quite different to Excel formulas and tricky to master. Power Query shields you from the code by hiding the Formula Bar. But you can click View > Formula Bar to show it in the Power Query Editor.

Advanced users can manipulate this code by using the Advanced Editor on the Home tab, or directly in the Formula Bar.

The steps that a user has performed are recorded and shown in the Applied Steps pane.

A user can view, edit and delete the steps from here. Power Query does not have an undo button. Removing a step is the undo of Power Query.

All of these steps are saved in the query. The query can then be loaded ready for analysis by clicking Home, the list arrow on the Close & Load button and then Close & Load To.

You can load your data into a table in Excel, straight into a PivotTable or just as a connection.

Loading it directly into a PivotTable enables us to avoid the physical limitations and strain on Excel. You can avoid monstrous file sizes because you are not actually storing the data in Excel.

By loading as a connection you can load much larger datasets than what Excel can handle (over 1 million rows of data), still keep your file sizes lean by not storing them in Excel and take advantage of more advanced calculations by using Power Pivot (our next Power tool to discuss).

Queries can be run again in the future by clicking the Refresh button on the Data tab, or using the Queries and Connections pane in Excel.

Imagine the potential here for regular data imports and reports that you may create. The whole process could be reduced to the click of the refresh button.

For more on this powerful tool, check out our rundown of the best Power Query tips.

Step up your Excel game

Download your cheatsheet of the best Excel shortcuts for PC and Mac

What is Power Pivot?

Power Pivot is used to model your data and perform more complex calculations than Excel can handle.

Power Pivot is great when working with huge data sets. Once Power Query has imported and cleaned the various data sources, Power Pivot is used to establish relationships between the tables/queries.

Then using DAX (Data Analysis Expressions), the formula language of Power Pivot, you can create more powerful calculations and more sophisticated data models than you can in Excel alone.

You should check out the definitive guide to using Power Pivot for more detail than this article will go into.

Power Pivot was initially only available as an add-in, but from Excel 2013 has been part of native Excel. You can access Power Pivot by clicking the Go to the Power Pivot Window button on the Data tab.

Alternatively, you can enable the Power Pivot tab on the Ribbon. This is hidden by default.

Right mouse click anywhere on the Ribbon and click Customize the Ribbon.

Check the box for Power Pivot on the right and click Ok.

The Power Pivot tab is shown below. It has a button to open the Power Pivot window (just like the Data tab), to create measures (DAX formula) directly in Excel and a few more.

The Power Pivot window has two views. The Data view looks similar to Excel and enables you to see your data and create calculated columns and measures using DAX formulas.

And the Diagram view where you can establish the relationships between your tables.

When your model is set up, you can analyze and report on your data using PivotTables.

The field list below shows a PivotTable using three tables from our Power Pivot model.

What is Power BI?

Power BI is a term used to describe two different tools - Power BI Desktop and Power BI Service (also referred to as PowerBI.com).

Power BI Desktop is a separate tool to Excel and is free to download and use. There are also paid versions of Power BI Desktop (Pro and Premium) which provide greater collaboration for businesses.

This tool uses the same Power Query and Power Pivot engines to get, clean and model your data ready for reporting. So when you learn these tools in Excel, you are learning them for Power BI Desktop also.

In the image below, you can see similar buttons to what you find in the Power Query and Power Pivot tools. And then more for reports and publishing.

Power BI strengths over regular Excel are in its visualizations. Visualizations is the name assigned to the charts, slicers, tables and matrixes that you can insert into your reports in Power BI.

Power BI has a greater choice of visualizations than Excel, and lots of options for how they interact with the other visualizations on a page, or even across the entire report.

This list is constantly growing, with more available to download from the marketplace and the ability to create your own custom visuals.

Power BI Desktop is where you create your model, perform your DAX calculations and build your reports. These can then be published to PowerBI.com.

Once published, others can view and interact with your reports at PowerBI.com. They can do this via any browser or mobile device.

Excel Power Query Tutorial

You can also create dashboards using the PowerBI.com service to show the highlights from your reports. And refresh existing reports shared with you.

To get some practice under your belt, follow our step by step guide to creating a Power BI pie chart.

How do these tools work together?

Power Query and Power Pivot do not both need to be used. They are independent tools and you may just need one or the other.

Power Query can prepare simple but large datasets for analysis. Power Pivot is for more complex models and more powerful calculations than Excel worksheet functions or PivotTables alone.

Although they can be used independently these two tools do complement each other. Power Pivot contains features for importing and shaping data, but it is recommended to leave this job to Power Query.

The data is then loaded to the model for Power Pivot to begin establishing relationships and creating measures using DAX.

Power BI is a completely separate tool that has both of these tools included. It has more connection options than Power Query in Excel. It also has better visualizations and can publish to the Power BI Service for better sharing of reports.

Power BI can be like bringing a hammer to mash your avocado though and Excel is the product that workers recognize and love.

And there are benefits to speaking people’s language by using the tools in Excel, unless you require the better visuals and other capabilities that Power BI provides.

Time to power up

These three power tools can take you beyond the physical limitations and calculations available in regular Excel.

Power Query and Power Pivot are available in Excel so anybody can start to explore their capabilities and use them in their data retrieval, shaping and analysis.

Power BI Desktop takes things further with its extensive interactive visualizations and ability to share reports in any device.

If you are looking to develop your skills beyond standard Excel, GoSkills offers dedicated courses on Power Query, Power Pivot, and Power BI that will quickly get you up to speed.

Get started today with a 7 day free trial of all GoSkills courses.

Excel Power Query Examples

Step up your Excel game

Add Power Query To Excel 2013

Download your cheatsheet of the best Excel shortcuts for PC and Mac