WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Power Query Tutorial

Power Query Tutorial

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.

Excel Power Query Tutorial

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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.

Power Query Tutorial Example 1

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

Excel Power Query Tutorial Example 1.16

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

Power Query Tutorial Example 1.1

Step #3 – This will display the preview of the data before it loads to power query model, and it looks like this.

Example 1.2

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.

Power Query Tutorial Example 1.3

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.

Example 1.4

Step #6 – Now come to excel and select the table, and it will show two more tabs as “Query & Table Design”.

Excel Power Query Tutorial Example 1.5

Step #7 – Under “Query” click on “Refresh” button and data will be refreshed with updated two new rows.

Example 1.6

  • There is another problem here, i.e. first row is not captured as a column header.

Example 1.7

Step #8 – To apply these changes, click on “Edit Query” under “Query” tab.

Excel Power Query Tutorial Example 1.8

  • This will open the power query editor tab.

Excel Power Query Tutorial Example 1.9

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

Excel Power Query Tutorial 1.10

  • So this will make the first row as a column header, and we can see this below.

Excel Power Query Tutorial Example 1.11

Step #10 – Click on “Close & Load” under the HOME tab, and data will be back to excel with modified changes.

Excel Power Query Tutorial Example 1.12

  • Now in excel, we have data like this.

Excel Power Query Tutorial Example 1.13

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.

Excel Power Query Tutorial Example 1.15

  1. Ribbon – This is just like our MS Excel ribbons, under each ribbon, we have several features to work with.
  2. List of Queries – This is all the tables imported to excel in this workbook.
  3. Formula Bar – This is like our formula bar in excel, but here it is M Code.
  4. Data Preview – This is the preview of the data of the selected query table.
  5. Properties – This is the properties of the selected table.
  6. 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 –

  • Power BI Query
  • Features of Power BI
  • Power BI DAX
  • Power Bi Tutorial
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More