Power Query Tutorial Step By Step Guide With Examples

Power Query Tutorial

Cleaning and getting the data from multiple sources is the biggest challenge for Excel users. Microsoft has introduced “Power Query” for Excel to aid all these. So, if you are new to Power Query, we will take you through this tutorial.

Power Query is an additional tool available for Excel from Excel 2016 onwards versions. But, you can also use Excel 2013 and 2010 versions as an add-in. However, when we get the data from external sources to Excel, data will be in an ugly format and almost unworkable with that data. We know you have experienced this!

But using Power Query in ExcelPower Query In ExcelPower Query is an excel tool used to import data from different sources, transform (change) it as required, and return a refined dataset in the workbook.read more, we can access, transform, and clean all the ugly data into a readable format. We can also use Power Query to write queries and reuse those queries to get the updated data by clicking on the “Refresh” button.

With all the above features, Power Query is still user-friendly. You can learn this much easier than you have learned MS Excel in the first place.

You need not have any programming language experience for this, unlike VBA.

Note: The Excel 2016 version Power Query is available as “Get & Transform Data” under the “Data” tab. But, in the Excel 2010 and 2013 versions is an add-in. You can use this link to download the Power Query add-in.

Make sure you download the version (32-bit or 64-bit) as per the MS Excel bit version. After installing this, you should see this as a separate tab.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Power Query Tutorial (wallstreetmojo.com)

How to Work with Power Query?

Working with Excel Power Query is just fun because of its user-friendly options. It has so many features that we will try to give some examples.

Example – Import Data from Text File

Getting the data from a text file is common. A delimiter value separates each column. For example, look at the below data table.

Introduction to Power Query Window

When you look at the Power Query window, you must be confused. So, let us introduce you to the Power Query window.

Go to the “Data” tab, and under “Get Data,” click on “From File.” Under this, click on “From Text / CSV.” Now, it will ask you to choose the file you would like to import, so choose the file and click on “OK.” It will display the data preview before it loads to the Power Query model. It looks like this. As you can see above, it has automatically detected the delimiter as a “Comma” and segregated the data into multiple columns. Click “Load” at the bottom, which will load data to an Excel file in Excel table format. On the right side, we have a window called “Queries & Connections,” which suggests that data is imported through a Power Query. Once the data is loaded to Excel, the connected text file should be intact Excel. So, go to the “Text File Data” and add two extra data lines. Now, come to Excel and select the table. It will show two more tabs: “Query” and “Table Design.” Under “Query,” click on the “Refresh” button. It will refresh data with updated two new rows. There is another problem here, i.e., the first row is not captured as a column header. Click “Edit” under the “Query” tab to apply these changes. It will open the “Power Query Editor” tab. It is where we need a Power Query. To make the first row a header, under the “Home” tab, click “Use First Row as Header.” So, this will make the first row a column header. We can see this below. Click “Close & Load” under the “Home” tab. It will return data to Excel with modified changes. Now in excel, we have data like this. Without changing the actual position of the data, the Power Query modified the data.

As you can see above, it has automatically detected the delimiter as a “Comma” and segregated the data into multiple columns.

On the right side, we have a window called “Queries & Connections,” which suggests that data is imported through a Power Query.

It is where we need a Power Query.

Without changing the actual position of the data, the Power Query modified the data.

Ribbon – This is just like our MS Excel ribbonsMS Excel RibbonsThe ribbon is an element of the UI (User Interface) which is seen as a strip that consists of buttons or tabs; it is available at the top of the excel sheet. This option was first introduced in the Microsoft Excel 2007.read more. Under each ribbon, we have several features to work with.List of Queries – This is all the tables imported to Excel in this workbook.Formula Bar – This is like our formula bar in Excel, but here it is M Code.Data Preview – This is the preview of the data of the selected query table.Properties – It is the properties of the selected table.Applied Steps – The most important. All the applied steps in the Power Query are displayed here. We can undo actions by deleting the queries.

It is the introductory tutorial to the Excel Power Query model. We have many other things to do with Power Query and will see those in the coming articles.

Things to Remember

The Power Query is an add-in for Excel 2010 and 2013, so we must install it manually.In Excel 2016, the Power Query is under the “Data” tab in the “Get Data” tab, which is in the “Get & Transform Data” section.