Power Query: the 2019 Definitive Beginners’ Guide

  • 17 min
  • Financial Modelling
  • By Edouard Ouaknine, 28/10/2019

Hi, welcome to the beginners’ guide to Power Query by UpSlide.
Have you ever wondered what Power Query is? How it works? If so, you are in the right place!
Pauline, our Power Query expert from Finance 3.1, gave us all her best tips and advice to write this article.
Here you will learn the basics of the software and even get to practice a bit! Are you ready?

If you don’t have time to read it now, download the PDF version and enjoy it later!

Part 1: Frequently asked questions about Power Query

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

Power Query is an Excel add-in that allows its users to import, clean and consolidate data. You can 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!

Power-Query-funnel

Power Query is a user-friendly and powerful tool even for beginners, as you don’t need coding experience to get started. Moreover, it does not change the original data source. The outcome is like a snapshot of reshaped data brought to Excel. What’s so great about that? Well, 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!).

To sum up, Power Query is a data analysis tool making ETL (Extract-Transform-Load) processes both more simple and effective.

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

Power Pivot is often associated with Power Query because it is a Microsoft Office add-in that eases data analysis. Interestingly, however, these two tools can work in tandem with each other! Power Pivot is simply the next step of data transformation: it allows users to create data models from tables previously cleaned by Power Query.

While similar, Power Pivot is more-so used for data analysis, time-related calculations and generating links generation. Simply said:

  • 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 underlying Query technology of Power Query is also included in Power BI.

The interface of the Power BI Desktop software allows you to benefit from the 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.

4. Why is Power Query a great 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. No coding skills needed!
  • Visualize your operations step by step without running a single macro.
  • Easily edit the steps order of different steps through dragging-and-dropping! Also, you can add and delete unnecessary steps by 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, sending emails, etc.

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

YES. Power Query is a free extension of Excel. That is another main benefit of the add-in.
(Read next question to find out how you can get it on your Excel desktop)

Power Query for Mac? YES, big news: Query editor is now compatible with Excel for Mac! But the integration is quite limited at the moment as you can only change the file path and refresh existing queries.

 

Part 2: Get started with Power Query!

1. How to download Power Query?

Power Query has been available for Excel since 2010.

Here is how to add Power Query according to your Excel version*:

  • Good news, you do not need to download Power Query for Excel 2019, 2016 or in Office 365. You can find it in the “Data” ribbon under the button “Get & Transform”.
  • For Excel 2013 and 2010, you have to download the Power Query add-in. It will appear in a new dedicated tab named “Power Query” (in Excel 2010 you need to have a Professional Plus with Software Assurance license).

*Here is how to check your 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.

To learn more, here is another detailed article about how to install and activate Power Query in Excel.

2. How do I activate Power Query in Excel?

Depending on your Excel version (see above question: Where is Power Query in Excel?) you just need to click the button named “Get data” in the “Get & Transform” section in the “Data” tab for Excel 2016 and beyond. For Excel versions prior to 2013 included, simply go to the 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.

3. 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 (“Power Query” ribbon for Excel 2010 and 2013).

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 4 distinct elements:

Main ribbon: lets you 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 4, you will learn how Power Query basically works by transforming a data table.
  • In section 5, it gets even more interesting as you will discover how to proceed data from various sources and merge queries.

4. 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, 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?

Wouldn’t that be awesome? 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.

(if you have Excel version 2013 or 2010, simply go to the Power Query tab)

  • 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.
Congrats, you are now connected to the data source “Exchange Rates”. This is the first step of your query. In order to flatten the data, we are going to apply further steps (see the Queries pane)! A query is basically a connection to a data source.

You may have also noticed that some applied steps have already appeared in the Query Settings pane. These are done automatically.

Now let’s practice a bit, starting by cleaning 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.

You’re doing good! Three more steps and you will be all set!

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

5. Let’s go further with 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 managers sent you.

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

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

Now we will see 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 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.

Congrats! 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 now know how Power Query works.

Now it’s your turn!

I want to turn it over to you: did you understand all the above questions about Power Query?
Did you download our tutorial files and follow the above guidelines to try it on your own?
Let us know by leaving a quick comment below right now!

To learn more about Power Query:

Post a comment

avatar
  Subscribe  
Notify of