Power Query Tutorial

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

Power Query is an additional tool available for Excel from Excel 2016 onwards versions, but you can use to for Excel 2013 & 2010 version also as an add-in. 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 Excel Power 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. Not only that Power Query can be used to write queries and we can reuse those queries to get the updated data by just clicking on “Refresh” button.

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

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

Note: In Excel 2016 version power query is available as “Get & Transform” under “Data” tab but in Excel 2010 & 2013 version it 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 MS Excel bit version. After installing this, you should see this as a separate tab.

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

How to Work with Power Query?

Working with Excel Power Query is just fun because of user-friendly options, and also it has so many features in it we will try to give some example here.

Example – Import Data from Text File

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

We will use power query to import this data and transform it the format which excel loves working with.

Go to Data tab and under get data click on From File and under this click on “From Text / CSV”.
Now it will ask you to choose the file that you would like to import, so choose the file and click on “Ok”.
This will display the preview of the data before it loads to power query model, and it looks like this.

As you can see above, it has automatically detected the delimiter as “comma” and segregated the data into multiple columns.
Click on “Load” at the bottom and data will be loaded to excel file in excel table format.

As you can see to the right side, we have a window called “Queries & Connections”, so this suggests that data is imported through power query.
Once the data is loaded to excel the connected text file should be intact with excel, so go to the text file and add two extra lines of data.
Now come to excel and select the table, and it will show two more tabs as “Query & Table Design”.
Under “Query” click on “Refresh” button and data will be refreshed with updated two new rows.
There is another problem here, i.e. first row is not captured as a column header.
To apply these changes, click on “Edit Query” under “Query” tab.
This will open the power query editor tab.

This is where we need power query.
To make the first row as header under HOME tab, click on “Use First Row as Header”.
So this will make the first row as a column header, and we can see this below.
Click on “Close & Load” under the HOME tab, and data will be back to excel with modified changes.
Now in excel, we have data like this.

Without changing the actual position of the data, power query modified the data.

Introduction to Power Query Window

When you look at the power query window, you must be confused, let us introduce you to the power query window.

Ribbon – This is just like our MS Excel ribbonsMS Excel Ribbons The 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 – This is the properties of the selected table.
Applied Steps – This is the most important, all the applied steps in the power query displayed here. We can undo actions by deleting the queries.

This is the introductory tutorial to excel Power Query model, and we have many other things to do with Power Query and will see those in coming articles.

Things to Remember

Power Query is an add-in for Excel 2010 & 2013 version, so need to install manually.
In Excel 2016 version Power Query is under the DATA tab in the name of Get & Transform Data.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Power Query Tutorial