A Beginner’s Guide to Power Query, Power Pivot and Power BI

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. 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.

Power-Query-import-data

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.

Power-Query-editor

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.

Power-Query-editor-formula-bar

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

Power-Query-advanced-editor

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

Power-Query-applied-steps

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.

Power-Query-close-load

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

Power-Query-import-data

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.

Power-Query-queries-connections

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.

Power-Pivot-ribbon

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.

Power-Pivot-customize-ribbon

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

Power-Pivot-customize-ribbon-2

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.

Power-Pivot-tab-ribbon

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.

Power-Pivot-data-view-DAX

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

Power-Pivot-diagram-views

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.

Power-Pivot-pivot-table-fields

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-tools-ribbon

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-visualizations

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.

Power-BI-visualizations-options

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.

Power-BI-report

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.

Excel Power BI Power BI

Power Query

Power Pivot

Desktop & PowerBI.com

Role

Import and shape data

Data modeling and calculations

Complete business intelligence tool

Language

M

DAX

M and DAX

Key strengths
  • Nice easy to use interface
  • Powerful tools to import and clean data
  • All Excel users can benefit from this tool
  • Easily handle millions of rows of data
  • Modeling tools for efficient data storage and analysis
  • Powerful DAX calculations going beyond standard Excel
  • Incredible visualization options
  • Simple built-in interactive options
  • Powerful DAX calculations
  • Simple publishing to PowerBI.com and mobile devices

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.

Add a Comment

Your email address will not be published.