This is a guest post by Puneet Gogia.

Power Query is a revolution. It’s a (part of a) complete self-service BI tool which can help you in importing, connecting, shaping, cleaning and sharing your data with little efforts.

It has the capability to automate every single data process of your work. And, you should start using it for your little/big all kind of data tasks.

If you are using Excel 2016 then power query is already there as “Get & Transform” or otherwise you can download it from here. So today, in this post, I will show you the 5 basic tasks which you can perform with power query with very little efforts.

Table of Content

1. Combine Files
2. Unpivot Data
3. Merge Columns
4. Split Columns
5. Convert Date In Quarter

Featured Download: Get this cheat sheet to learn these 5 power query tips.

1. Combine Data from Multiple Excel Files

I have found that power query is a far better way to combine different files into a single one comparing to other methods. Let’s take an example.

Here you have 12 different files in which you have month wise sales data (one file for each month). In each file, you have date wise sales. you can download this data folder from here.

Before you create any report from this data you need to combine it into a single table. And, if you do it manually it will take minutes. But with power query, when you create a query, it’s a one-time setup and after that a single click every time, actually a simple refresh.

Let’s follow these steps to consolidate data from the multiple files. These steps are from the latest version of power query add-in so make sure to update if you have an old one.

• Go to data tab -> Get and Transformation -> New Query -> From File -> From Folder.

• After that, using the dialog box select the folder where you have all the files and click OK to load.

• Now, you’ll get a list of all the files along with details.
• From here, click on combine and edit.

• Next, you have to select the parameter to extract from each file. Here we have data in the “Sheet1” worksheet in each file, so select it and click OK.
• Once you click OK, it will load the combined data into the power query editor.

• Now, here you have three data columns. Two are from the files and another one is created by the power query to list file names with the data.

• If you want to delete that column, just right click on it select “Remove”.

• After that, click on “Close and Load”.

That’s it. All done. You have successfully combined all the files.

2. Unpivot Data

This is the another thing which you can do with power query to save your ton of time. Have a look at below data table. It looks good but you can’t use it further.

So the best way is to unpivot it and the best way to unpivot it is to use power query.

Steps:

• Click any of the cells in the table. Go to Data Tab -> Get & Transform -> “From Table”.

Quick Tip: When you click on “From Table” make sure your data is an excel table. Or if it’s not, excel will first prompt you to convert it.

• Once you click on it, it will open power query editor for you with the table.
• Now, select all the four columns other than the first one.
• Right click and select unpivot columns.

• It will instantly unpivot your data which you can use further.

• After that, click on “Close & Load”.

Perfect.

3. Merge Columns

What you do when you need to merge data from two or more columns? Please share with me in the comment section.

And, here I will show you how you can use power query to do it.

Steps:

• Select the table and go to Data Tab -> “From Table”.
• In power query editor, select both of the columns and press right-click.
• And from the right click menu, select “Merge Columns”.

• From the merge column dialog box, select separator “Space” and add “Full Name” in the column name.

• Now, click OK.

• In the end, click “close and load”.

This will convert those two columns in a single merged column.

4. Split a Column

Now, let’s backtrack and again split the column between the first name and last name which you have merged in above example.

Steps.

• Select the table and go to Data tab -> From Table.
• In power query editor, right-click on the column and select split column.

• Select “Space” as a delimiter and click OK.
• And now, that single column is split into two different columns.

You can also use a different delimiter instead of space.

5. Convert Date In Quarter

Let’s say you want to summarize your data quarter wise. Instead of using formulas you can use power query to convert a date into quarters.

And, to convert a date into a quarter you have two different methods in power query. You can use any of these two which think is perfect for you.

Method-1

In this methods, you can convert dates into a quarter simply using below steps.

• Select the table and go to Data tab -> From Table.
• In power query editor, select the date column and right click on it.
• Go to Transform -> Quarter -> Quarter.

• And it will convert all dates into quarter numbers (like 1, 2, 3, and 4).

• Now again select that column and go to transform tab -> Text Column -> Add Prefix.

• In prefix input box, enter “Q-” and click OK.

• It will convert all the dates into quarters with a prefix “Q-“.

Method-2

In this method, instead of converting a data into a quarter, you can create a new custom column and use a formula.

Steps.

• Select the table and go to Data tab -> From Table.
• Now in power query editor, go to “Add Column Tab” and click “Custom Column”.

• In the custom column dialog box, enter name “Quarter” and enter below formula in the formula input box.

=”Q-” & Text.From(Date.QuarterOfYear([Date])

• Click OK and it will insert a column with the quarter for each date.

• In the end, click close and load.

Conclusion

The more you use power query the more you will find that how much it can save your precious time. All the methods which you have learned above are huge time savers.

And, I hope you find it useful.

If you have any favorite task which you often performs with power query, please share with me in the comment box. I would love to hear from you.

About The Author

Puneet is the founder of ExcelChamps. You can find him online, tweeting about excel, on a running track, or sometimes hiking up a mountain. Download his Excel Productivity Guide for some amazing time saver tactics.

MF’s comment

Thanks Puneet for such a great post on Power Query, which you may have been impressed already.  Indeed Power Query can do much much much more.  Don’t limit yourself with traditional Excel.  Excel has been evolving.   If you don’t evolve with it, you will be left behind.