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 Excel, 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.
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.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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.
Step #1 – Go to Data tab and under get data click on From File and under this click on “From Text / CSV”.
Step #2 – Now it will ask you to choose the file that you would like to import, so choose the file and click on “Ok”.
Step #3 – 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.
Step #4 – 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.
Step #5 – 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.
Step #6 – Now come to excel and select the table, and it will show two more tabs as “Query & Table Design”.
Step #7 – 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.
Step #8 – 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.
Step #9 – 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.
Step #10 – 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 ribbons, 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.
Recommended Articles
This has been a guide to Excel Power Query Tutorial. Here we discuss step by step guide of how to work with Power Query along with examples. You can learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
