Guest Post: Three Things You’ll Love Doing in Power Query

[Note: This guest post is from Aprajita Sharma at ExcelChamps.com. I am a friend of ExcelChamps co-founder, Puneet, and look forward to learning from Aprajita as well. Today she shares how to perform common data wrangling tasks in Power Query — see for yourself, these can be a HUGE productivity booster and I do think you’ll LOVE doing them.

If you haven’t explored Power Query yet — it’s like a sleeping giant lurking in your spreadsheets — but a sleeping giant that wants nothing more than to help you clean and analyze data.

Take it away, Aprajita! –George]

If there is one thing which makes me excited these days, that’s Power Query. But let me ask you something: Do you use it? and Do you use it to its full?

If your answer is “YES” then let me tell you that I’m proud of you. Yes, really proud. And if you are not using it, then today I want you to start!

Truly, you need to LEARN POWER QUERY and I want to tell you about a special person who’s here to help you:

Oz D Du Soleil [Excel On Fire]

But now, let’s get back to the business and today I’d like to share with you  how to…

• Insert an Index Column with Power Query

• Split One Column into Two

• Merge Columns into One

…so let’s get started.

What Is Power Query?

Power Query is a tool introduced by Microsoft that can help you reshape your data in real-time. With Power Query, you can collect data from multiple sources, transform it into the form you want and reload it into the Excel worksheet. Check out this amazing example about combining multiple Excel files into one to see how powerful Power Query is.

Why I Should I use POWER QUERY if VBA and Formulas are there?

That’s right, you can do a lot of things with VBA and formulas to solve almost every problem and automate your tasks.

But with POWER QUERY you can create queries which are real-time and you can run them whenever you need.

For a great audio introduction to Power Query, check out this PODCAST episode by John Michaloudis from MyExcelOnline.

How to Activate it

If you are using Excel 2016, 2019, or Office 365 there Power Query is there as a in-built feature as “Get & Transform Data”. You can find all the Power Query options in your data tab.

And if you are using Excel 2010 or 2013 you can install it as a free add-in for this from Microsoft. Check out this link to download this free add-in and install and then follow the below steps to activate it.

• First of all, click the File tab and select Add-ins.
• Now you have a window opened with a long list of add-ins.
• After that go to the last option Manage.
• Next, you need to click on the dropdown list next to Manage and select COM Add-ins and click GO.
• As a result, a dialogue box COM Add-ins appears and now you need to select Microsoft Power Query for Excel.
• Lastly, Click OK and your tab is here.

Adding an Index Column (Serial Numbers)

With Power Query, you can easily add an Index Column on a few clicks once the data is loaded to Power Query Editor.

As a rule of thumb, convert your data set to a table from a range or  Power Query will ask you to do so.

Here we have a list of 50 names (download here) where we are going to add an index column from 1-50, follow the below steps for this.

• First of all, click on the “Add Column” tab.

• After that, look for the index column and click on the down bar.

• Now here you have 3 options: From 0, From 1 and Custom. That means you have an option to start your Index from 0, 1 or custom. let’s click on “From 1”.
• The moment you click, Power Query will add a new column with index numbers starting from 1. 

• Here you got the INDEX column but towards the right side of the data column. No problem, we still have the solution. 
• To move the index column before the names column:
  • Click on the Transform button.
  • Now look for “Move” in “Any Column”
  • Here click on “To Beginning”.

• In the end, you need to load this data into your worksheet and for this, simply click on the “Transform” button from the home tab.

Split a Column into Two or More

Just like you use a formula or text to column option, you can also use Power Query for splitting a column as well.

In this example, we will be using the same name data where we have names in a column and split column into the two columns for first name and last column.

I assume you are already in the power query editor. So let’s follow the below to do this:

• To begin with, first of all, go to the Transform tab → Text Column → Split Column.

• Now, choose from the drop-down list, based on your data how you want to split your cell.
• In this example, you need to split the first name and the last name (you have a space between them) into two different columns, and so you need to select space as a delimiter.

• In the end, you just need to click OK apply it and here you go, you have two columns with first and last names separately. 

Quick Note: Make sure to rename both of the columns (first name and last name) before you load data into the worksheet.

Merge Two or More Columns into One

Generally, we use & operator and concatenate function to combine multiple cells. The same thins you can do with a power query.

Let’s take an example here: the same two columns which you just have split in the last example. Let’s rejoin them using Power Query.

Below are the steps you need to follow:

• First of all, you need to select both the columns you want to merge.

• Now go to the Transform Tab → Text Column → Merge Columns.

• Here you have a dialogue and from this dialogue box, select the “Space” as separator and add column name “Full Name”.

Yes, that’s it, your work is done and here’s your new column.

In the end

Power Query is one of those tools which can help you to better in crunching data and reshaping it quickly and in real-time.

And I hope you have found the tips which I have shared with you above.

Which one is your favorite out of these?

Share your views with me in the comment section. I’d love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it.

About the Author

Aprajita is the Co-Founder of ExcelChamps. She has an MBA in Sales and Marketing and has been using Microsoft Excel for the last 8 years. Her journey started from learning a basic pivot table from Google which made her fall in love with Excel.