Power Query: the 2022 definitive beginners’ guide

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! 

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!

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

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. 

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.

How does Power Query work?

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

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:

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

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

Click on Transform Data.

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

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

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

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.

Problem: each 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).

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

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.

Now replace“€” with “EUR”.

Go to Home tab > 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.

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.

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

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.

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.

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’ve now mastered one of the best Microsoft Office add-ins, Power Query. 

To learn more about Power Query: