Table Of Contents

arrow

Power Query Tutorial

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

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.

Power Query Tutorial Example 1

We will use a Power Query to import this data and transform it into a format that Excel loves working with.

  1. Go to the "Data" tab, and under "Get Data," click on "From File." Under this, click on “From Text / CSV.”


    Excel-Power-Query-Tutorial-Example-1.16

  2. Now, it will ask you to choose the file you would like to import, so choose the file and click on “OK.”


    Power-Query-Tutorial-Example-1.1

  3. It will display the data preview before it loads to the Power Query model. It looks like this.


    Power-Query-Tutorial-Example-1.2

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

  4. Click “Load” at the bottom, which will load data to an Excel file in Excel table format.


    Power-Query-Tutorial-Example-1.3

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

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


    Excel-Power-Query-Tutorial-Example-1.4-1

  6. Now, come to Excel and select the table. It will show two more tabs: "Query” and “Table Design.”


    Excel-Power-Query-Tutorial-Example-1.5

  7. Under “Query,” click on the “Refresh” button. It will refresh data with updated two new rows.


    Excel-Power-Query-Tutorial-Example-1.6

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


    Excel-Power-Query-Tutorial-Example-1.7

  9. Click “Edit” under the “Query” tab to apply these changes.


    Excel-Power-Query-Tutorial-Example-1.8

  10. It will open the "Power Query Editor" tab.


    Excel-Power-Query-Tutorial-Example-1.9

    It is where we need a Power Query.

  11. To make the first row a header, under the "Home" tab, click “Use First Row as Header.”


    Excel-Power-Query-Tutorial-1.10

  12. So, this will make the first row a column header. We can see this below.


    Excel-Power-Query-Tutorial-Example-1.11

  13. Click “Close & Load” under the "Home" tab. It will return data to Excel with modified changes.


    Excel-Power-Query-Tutorial-Example-1.12

  14. Now in excel, we have data like this.


    Excel-Power-Query-Tutorial-Example-1.13

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

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.

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 - It is the properties of the selected table.
  6. 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.