Power Query: the 2022 definitive beginners’ guide

  • Efficiency tips How to guides Power BI

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

Pauline, our Power Query expert at UpSlide, will share all her top tips so that you can master the basics of this software to boost your productivity in Office. If you don’t have time to read our guide now, bookmark this article and save it for later! 


  • Compare the key differences between Power Query and Power Pivot, Power BI and Excel VBA.

  • Download Power Query and get started. Identify the 4 key elements of the Power Query Editor: the main ribbon, queries, data table preview and query settings.

  • Shape your first data table with our step-by-step guide. Plus, discover how to use advanced Power Query skills in this real-world problem scenario.

What is Power Query? When should I use it?

Power Query is a user-friendly and powerful Office 365 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.

Thanks to Power Query, I can easily flatten tables for my models.

Pauline Jouffret Data Engineer

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.

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

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. 

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

How to download Power Query

The good news is – you do not need to download 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

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.

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.

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.


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


Go to Home tab > Merge Queries.

power query merge queries

A 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

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

power query

Wow! If you made it to the end, congratulations! You’ve now mastered one of the best Microsoft Office add-ins, Power Query. 

power query

To learn more about Power Query:

Pauline Jouffret Pauline is a Data Engineer at UpSlide. After 4+ years as a seasoned Consultant and Project Lead at our sister company, F31, Pauline now oversees the management and analysis of data across UpSlide.
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Abir Mahmud
Abir Mahmud
3 years ago

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

Edouard Ouaknine
3 years ago
Reply to  Abir Mahmud

Hi Abir,

To remove the header rows of the second file of sales data in Power Query, go to Home tab > Remove rows > Remove top rows and type “1” if you want to remove the first row only (you can type the number of header rows you want to remove).

Let us know if it helped or if you have any other questions!

Kelly A
Kelly A
3 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
2 years ago

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

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

1 year ago

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

Want to see more?

See more
Branding and design
5 minutes

How to manage email signatures in Outlook

In today’s digital world, successful businesses must effectively and consistently present their brand identity...
Efficiency tips
5 minutes

4 effective ways of connecting Power BI to PowerPoint

More and more financial professionals have been adopting Power BI, the Microsoft software that is revolutionizing...
Efficiency tips
5 minutes

7 easy ways to reduce your Excel file size

We have all encountered an Excel file that’s too large. The kind that takes minutes to open or save, so everything...