How to transform columns in Excel Power Query

When working with tables of data in Power Query, any operation by definition has to involve either operating on rows or columns of a table. A previous post showed some typical techniques for working with rows in Power Query. Let’s now move to columns.

This demonstration will cover some common column operations such as converting case, splitting columns and so forth. Go ahead and load the data into Power Query and let’s get started.

Changing case

Power Query makes it easy to convert a column of text from lowercase to upper, uppercase to proper and so forth. For example, hold down the Ctrl key and select both Title and Artist Name. From there, you can right-click on either column and select Transform > Capitalize Each Word:

Find and replace values

You may have noticed that both Title and Artist Name contain no spaces after colons or commas. Let’s fix that: right-click on either of the columns yet again and select Replace Values:

In the Replace Values menu, search for : and replace with : and an extra space:

Next, do the same with commas: replace with a comma and a space.

Keep in mind that Power Query records each step you apply to the data in Applied Steps. This makes it much easier to audit text changes over the classic Find and Replace menu.

Delimiting text

Another common Excel task made more reproducible in Power Query is delimiting text (Text to Columns). Right-click the Item # column and split it into two by selecting Split Column > By Delimiter > Space:

Change data types

In Power Query, every column has a specific data type. A proper data type enforces what operations can and can’t be done to a given column. By default, Excel tries to detect the best data type for each column in a dataset. But sometimes, improvements can be made.

For example, UPC has been given a Whole Number data type. While it’s true that UPC numbers never contain decimals, we’re probably not going to conduct any kind of significant math on this column either, such as adding or taking an average. In that case, it’s better to store the column as text than number.

With that said, click the number icon to the left of UPC and change it to Text.

Nice work. Continue on with the following data type changes:

• Convert ISBN 13 to Text
• Convert Retail to Currency

Deleting columns

Even (or maybe especially?) in the age of Big Data, it can be a relief to drop unnecessary column from a dataset. This is as easy to do in Power Query as it is in Excel — just highlight the BTkey column and press Delete to remove it from the dataset.

One big positive, of course, to the Power Query method is how much easier it is to retrieve this column of data once deleted — just go back to Applied Steps and remove it.

Formatting data types

If you don’t want to necessarily change the data type of your column, just how it’s displayed, you can tweak its formatting. This is is commonly done on date fields like Release Date. We’re going to create separate Year, Month and Day columns based on this data. The first step will be to right-click and select Duplicate Column. Do this twice so we have three date columns in total.

Click on the first Release Date column and select Transform > Year > Year. You will see this column get reformatted to only return the year number, rather than the full date.

Go ahead and transform the next two columns to give the month number and day number. You can rename your reformatted columns Year, Month and Day respectively.

Saving your column work

Nice work transforming columns in a variety of ways using Power Query! Be sure to save your work by heading to the ribbon, then click Close & Load.

Were you able to follow along with all the steps in Power Query? You can check your work below:

These are just some of the many tasks you might want to perform on columns in Power Query. If you’re interested in creating calculated columns, for example, check out this post.

Have you performed these or other column operations in Power Query? Let me know in the comments.