All You Need to Know About

Excel Power Query Tutorials

Microsoft Power Query for Excel accesses, cleans and transforms all that messy data and displays it in a way that Excel loves and can work with. You will be blown away by the data transformation potential of Power Query in Excel!

Here are the top things on what you can do with Power Query in Excel:

Consolidate Multiple Excel Workbooks Using Power Query

One of the most sought after query from the millions of Excel users around the world is:

How do I consolidate multiple Excel workbooks into one?

There are a couple of ways you can do this, using VBA or complex formulas but the learning curve is steep and out of reach for most Excel users.

Luckily with Power Query this consolidation task can be done in a couple of minutes!  That’s right, only a couple of minutes.

I show you how below…

DOWNLOAD EXCEL WORKBOOK

STEP 1: Create a New Folder on your Desktop or any directory and name it to whatever you like e.g. 2016 Sales

Move an Excel Workbook in this Folder that contains your Sales data e.g. January 2016.xlsx

move jan

STEP 2: Open a NEW Excel Workbook and go to Power Query > From File > From Folder

from folder

STEP 3: From the Folder dialogue box, click the Browse button

folder

This will bring up the Browse for Folder dialogue box and you need to select the folder you created in Step 1 and press OK

browse dor folder

STEP 4: This will open up the Query Editor.

From in here you need to select the first 2 columns (hold down the CTRL key to select) and Right Click on the column heading and choose Remove Other Columns

remove other columns

STEP 5: You need to go to Add Column > Add Custom Column

add custom column

STEP 6: This will bring up the Add Custom Column dialogue box.

In here you need to name the new column E.G. Import, and within the Custom Column Formula you need to enter the following formula:

= Excel.Workbook([Content])

This will import the workbooks from within the Folder that you selected in Step 3

add custom column2

STEP 7: You now have a new column called Import.

Click on the Expand Filter and select the Data box only and press OK.  This will import the workbook from the folder

import expand

STEP 8: Click on the Expand Filter from the Import.Data column and select OK.  This imports all the columns’ data from within the workbook

expand import 2

data import

STEP 9: Now it is time to transform the data by making some cosmetic changes!

Remove the Content column by Right Clicking and choosing Remove

remove binary

STEP 10: Select the Import.Data.Column1 and filter out the CUSTOMER heading and press OK.  This will also remove the other column’s headers

remove column headings

STEP 11: Select the Date column and go to Transform > Data Type > Date

data type date

STEP 12: Select the Sales column and go to Transform > Data Type > Currency

data type currency

STEP 13: Rename the column headings to whatever you like by double clicking on the column header, renaming and pressing OK

column rename

STEP 14: Go to File > Close & Load.

close & load

This will put the data into a new worksheet within your workbook

new sheet

STEP 15: You can now Insert a Pivot Table to do your analysis by going to Insert > Pivot Table > New/Existing Worksheet

insert pivot table

Put the Months in the ROWS and the Sales $ in the VALUES area:

pivot table1

STEP 16: NOW FOR THE COOL PART!!!!

You can move similar workbooks into the Folder we created in Step 1, say for subsequent months eg. February 2016.xlsx, March 2016.xlsx etc

NB: The Excel Workbooks have to have the same format and number of columns as in the workbook we imported in Step 1

move subsequent months

STEP 17: In your Excel workbook, click on the imported data and this will open up the Workbook Queries pane (If this does not open, go to Power Query > Show Pane)

Click the Refresh button (or you can go to Table Tools > Query > Refresh)

refresh pane

STEP 18: This will import the February 2016.xlsx and March 2016.xlsx data into the Excel workbook and append it to January’s data

feb data

STEP 19: Now you can Refresh the Pivot Table and the new imported data will be reflected

updated pivot

Next month all you have to do is drop in the new month’s workbook into the 2016 Sales Folder and Refresh the Query & the Pivot Table to see the results!

Replicating Excel’s LEFT Function with M in Power Query

Power Query lets you perform a series of steps to transform your Excel data. There are times when we want to do things that are not built in the user interface. This is possible with Power Query’s programming language, which is M.

Unfortunately not all of Excel’s formulas can be used in M.

For example, if we want to use the LEFT Excel Function, it is not supported in M.

But I have found a way for us to replicate the LEFT Function in M!

DOWNLOAD EXCEL WORKBOOK

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 merge-columns-02

Excel 2013 & 2010:

from table

STEP 3: This will open up the Power Query Editor.

Go to Add Column > Add Custom Column

We want to get the first 3 characters of the Sales Month:

STEP 4: Let us create a simple M expression to replicate the LEFT function in Excel.

In the New column name text box, type SALES MONTH (Shortened)

In the Custom column formula, type in: Text.Start(

From the Available columns choose SALES MONTH and Insert

Then finish off the formula by entering , 3) 

We now have build the following formula:

Text.Start([SALES MONTH], 3)

So lets quickly break down what we just did:

  • We are using the Text.Start formula to get the first X characters of the SALES MONTH column
  • We place in 3, to specify that we want the first 3 characters.

Click OK to confirm.

Now you will see your changes take place.

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Congratulations! You have used a M formula for replicating the LEFT function!

Unpivot Data Using Excel Power Query

Power Query allows you to extract data from any source, clean and transform the data and then load it to another sheet within Excel, Power Pivot or the Power BI Designer canvas.

One of the best features is to Unpivot Columns.

What that does is transforms columns with similar characteristics (e.g. Jan, Feb, March…) and puts them in a unique column or tabular format (e.g. Month), which then allows you to do further analysis using Pivot Tables which was not possible before unpivoting.

Here is how this is done:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Highlight your data and go to Power Query > From Table > OK

power query from table

STEP 2: This opens the Power Query editor and from here you need to select the columns that you want to unpivot

select column in editor

STEP 3: You then need to go to the Transform tab and select Unpivot Columns

unpivot columns power query

STEP 4: Go to the File tab and choose Close & Load

clos & load power query

STEP 5: This will load and open the unpivoted data into a new worksheet with your Excel workbook.  Now you can go crazy with your super analytical work, using Pivot Tables etc

unpivoted data

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]