Power Query: the 2022 definitive beginners’ guide

Ever wondered what Power Query is, and how it works? If so, you have come to the right article.

Pauline, our Power Query expert from Finance 3.1, will share all her top tips so that you can master the basics of this wonderful software. If you don’t have time to read our guide now, bookmark this article and save it for later! 

Part 1: Frequently asked questions about Power Query

1. What is Power Query? When should I use it?

Power Query is a user-friendly and powerful Excel add-in that allows you to import, clean and consolidate data. You’ll want to use Power Query when:

  • Processing large amounts of data (where Excel is limited to one million rows)
  • Importing data from different sources (e.g. Excel, CSV, SharePoint, SQL, and Salesforce)
  • Manipulating, cleaning and combining several tables, files or even folders!

Excel Power Query data sources

A key benefit of Power Query is that it doesn’t change the original data source. The outcome is like a snapshot of reshaped data brought to Excel. Since the source data is not directly imported, it does not make your Excel file heavier (i.e. it doesn’t slow down your computer!)

In summary, Power Query is a data analysis tool making ETL (Extract-Transform-Load) processes more simple and effective.

Power Query quote Pauline F31

2. What is the difference between Power Query and Power Pivot?

Power Pivot is often associated with Power Query as it’s also a Microsoft Office add-in that eases data analysis.

However, these tools typically work in tandem, as Power Pivot enables users to create data models from tables previously cleaned by Power Query. To summarize: 

  • Power Query allows you to automatically consolidate and reprocess files (i.e. the standardization of reporting processes or consolidation of several files with the same format).
  • Power Pivot enables you to analyze databases of several million lines by extracting aggregated information according to the rules set by your business.

3. What is the difference between Power Query and Power BI?

The interface of the Power BI Desktop software allows you to benefit from three technologies (Power Query, Power Pivot and Power View). With Power BI, you can do all the steps of the data analysis chain, from extracting data to creating visuals on an interactive dashboard that you can share and analyze.

> Discover the Power BI to PowerPoint or Word Link

Power Query Power BI data iceberg

4. Is Power Query a good alternative to Excel VBA?

Power Query can easily replace VBA (Visual Basic for Applications) as it enables you to:

  • Process your tables simply by clicking on buttons. Plus, no coding skills are needed!
  • Visualize your operations step-by-step without running a single macro.
  • Easily edit the order of steps by dragging and dropping! Plus, you can add and delete unnecessary steps with a simple click.

(see Part 2 – Section3: How to get started with Power Query?)

However, Power Query can’t replace VBA when it comes to interacting with Excel’s interface, automating processes, generating new files, and sending emails. 

5. Is Microsoft Power Query free? Is there a Power Query for Mac?

  1. Yes! Power Query is a free extension of Excel, which is another main benefit of the add-in. (Read the next question to find out how you can get it on your Excel desktop)
  2. Yes! Power Query is also compatible with Excel for Mac.

Part 2: Get started with Power Query

1. How to download Power Query

The good news is – you do not need to download or activate Power Query for Excel 2019, 2016 or Office 365. You just need to click the button named Get Data in the Get & Transform section in the Data tab for Excel 2016 and beyond. 

power query ribbon

Please note that when you enter the Power Query interface, you will no longer be able to interact with Excel. If you need to go back to Excel, you will have to close the Power Query editor.

How to check my Excel version 

Go to File > Account > About Excel. Then a window will appear. Read the first two numbers in the parenthesis. In the below example, the Excel version is 2016.

Power Query Excel version

2. How does Power Query work?

In Excel, you only have access to the following functionalities when you click on Queries & Connections:

Power query Excel data ribbon

Data ribbon: to search for data sources 

Queries & Connections pane: to have a quick look at all of the connections (queries) you made.

Once you launched Power Query, you land on the Power Query Editor which is made up of four distinct elements:

power query dashboard 2021

Main ribbon: to create queries from scratch thanks to its buttons (if you are familiar with the M language, you can also access the Advanced Editor to tweak them manually).

Queries: to keep track of all connections to data sources and streams.

Data Table preview: to visualize your data result after a specific step.

Query Settings (Applied Steps): to navigate through all the steps you made. Power Query automatically records all transformations made.

In the two last sections, you will be able to practice a bit thanks to an easy use case (you will need to get Power Query on Excel first):
  • In section 3, you will learn how Power Query basically works by transforming a data table.
  • In section 4, it gets even more interesting as you will discover how to proceed with data from various sources and merge queries.

3. How to get started with Power Query

Imagine the following situation:

You are the financial controller of a technology firm selling computers and smartphones. Currently, you’re operating in 3 countries: France, UK, and China. Each month, you produce a consolidated report for your manager, with KPIs such as total revenue, revenue by country or revenue by product. To do so, each country manager – Dennis, Douglas, and Karen – sends you a file.

Problem: each file lists the sales in the country’s own denomination. It is too time-consuming to convert them to Euros, and then consolidate them into the final report.

Power Query example usecase

What if you could automate the process and compute your report in a few minutes by only using Power Query?

Well, with Power Query, you can! Let’s start by preparing your first table.

First, you need data. Download our Power Query example and save it on your local server.

Then, follow these easy steps:

Unzip the file and save it to your local server.

Now open a new worksheet in Excel and go to the Data tab > Get Data > From file > From Workbook, as shown below.

Go to the path where you saved the unzipped files you just downloaded and select the Excel file named “Exchange Rates”.

Power query import table preview

Click on Transform Data.

Congratulations, you are now connected to the data source “Exchange Rates!”
In order to flatten the data, we are going to apply further steps (see the Queries pane)! You may notice that some applied steps have already appeared in the Query Settings pane. These are done automatically.

Now let’s clean the data table:

Remove the top 3 rows, go to Remove rows > Remove top rows and type “3”. You will instantly see the transformation on the Data table preview as well as in the applied steps (Query Settings).

Get rid of blank rows: Remove rows > Remove blank rows (as shown below).

Power Query remove blank rows

Promote your first row as a header: click Use First Row as Headers. As you may have noticed, Power Query automatically added a step to change the type of some data (the values in the first column are now formatted as Dates).

Power query use first rows as header

Remove blank columns: click Choose columns and uncheck columns 7, 8, 9 and 10.

Unpivot the columns: Select the “Exchange Rate” column, then in the Transform tab, go to Unpivot columns > Unpivot Other columns (you can also select the five other columns and select Unpivot columns).

Rename the columns: click on the header cells, enter “Date” in the first one, “Currency” in the second one and “Rate” in the third one.

Apply a filter: just like in Excel, click on the drop-down menu of the “Date” column and select “Dec-18″.

Power query filter

In the Home tab, click on Close and Load to load the final table in a new sheet.

Well done! You shaped your first data table.

4. Advanced Power Query

(Re)imagine the following situation:

Thanks to your Power Query skills, you are now a SENIOR financial controller of the technology firm. Now, you have to consolidate the December 2018 data that the country manager sent you.

Problemeach file lists the sales in the country’s own currency. It’s time-consuming to convert them to Euros, and then consolidate them into the final report.

What if you could automate the process and compute your report in a few minutes using Power Query?

Now we will show you how to consolidate different data sources and merge them with the table we prepared in the previous section.

* Please note that you need to complete all steps in the previous section before starting to manipulate the tables.

Firstly, let’s import the whole folder including the three different source files: If you’re still in Power Query go to New source > File > Folder.

(If back on Excel, go to Get Data > From Folder)

Select the folder “Sales_201812” you previously downloaded and saved. After that click OK.

Power-Query-folder-pathA new window should appear. Then, you need to merge the queries:

Select Combine & Edit in the drop-down menu (bottom of the new window).

Power query combine edit

Select the example table “Dec-18” (please note that all sheets must be named the same way).

Power query select Dec18

You can check in the first column that all sources are included (click on the drop-down menu), and then click OK.

You are now in the Power Query editor. Go to Transform tab > Replace Values.

Power Query replace values

Now replace“” with “EUR”.

power-query-replace-EUR

Go to Home tab > Merge Queries.

power query merge queriesA new window appears:

  1. Select “Currency” column.
  2. Select the “Exchange Rates” file in drop down menu.
  3. Select “Currency” column.
  4. Click OK.

Power query merge queries steps

Now, you need to display the exchange rate data that you just retrieved:

  1. Select “Exchange Rate” column.
  2. Click on the Expand button.
  3. Check only “Rate”.
  4. Untick “Use original …”.
  5. Click OK.

Congratulations! You just loaded another data source, merged queries and retrieved the corresponding exchange rate. No more VLOOKUP or INDEX+MATCH!

To finish, we will create a “Unit Price EUR” column to convert all prices to Euros:

Go to Add Column tab > Custom Column.

Power query custom column

A new window appears. Let’s write the formula of the Price in Euros:

  1. Name it “Unit Price EUR”.
  2. Double click on “Unit Price Local Currency” (or select it and then click on Insert).
  3. Type “/”.
  4. Double click on “Rate”.
  5. Click OK.

Once the column appears, click on its type symbol (just before the name) and select “Decimal Number”.

Power query set column decimal number

We have to do the same to generate the “Revenue” column:

Go to Add Column tab > Custom Column. A new window appears. Let’s type the Revenue formula:

  1. Name it “Revenue”.
  2. Double click on “Unit Price EUR” (or select it and then click on Insert).
  3. Type “*”.
  4. Double click on “Quantity”.
  5. Click OK.

    Power query generate Revenue column formula

    Again, change the column type to “Decimal Number”.

    To finish, let’s export it to a new data table in Excel!

    Go to Home tab > Close & Load > Close & Load.

    Power query close and load

    Now you can fully benefit from Excel features to analyze the data, make a pivot table, etc.

    Wow! If you made it to the end, congratulations! You now know how Power Query works.

    To learn more about Power Query:

    Subscribe
    Notify of
    guest
    6 Comments
    Oldest
    Newest Most Voted
    Inline Feedbacks
    View all comments
    Abir Mahmud
    Abir Mahmud
    2 years ago

    How to remove the header row of the 2nd file of sales data?

    Kelly A
    Kelly A
    2 years ago

    Thanks for this great post on Excel Power Query–a perfect resource for those who are just starting to understand how useful this tool can be. I’ll be saving it for future reference. I also came across another post that takes this information to the next level, and shows 5 actionable ways to use Power Query in the work setting. Here’s the link (https://beebole.com/blog/excel-power-query-for-business-intelligence/), in case it’s useful for anyone else reading this post.

    Beatriz Zambrano
    Beatriz Zambrano
    1 year ago

    Thanks a lot. Very useful and very easy to understand. Great post.

    joebee
    joebee
    1 year ago

    This is just awesome. Just starting to learn PQ and these helpful tips just gives me the grounding to learn PQ. Thanks so much.

    BrsSrl
    BrsSrl
    5 months ago

    that was really helpful. thank you ver much. I can’t imagine any tutorial prepared better than this one to understand.