Excel Power Query Add Column from Example

The Power Query Add Column from Example feature simplifies a task many of us perform daily. For example, how often do you add a column that references another column; either to extract part of it, perform a calculation or convert the data into something else? Or maybe you want to join columns together?

Let’s take column A in the example Table below that includes the name and position for each employee, which I want to separate into their own columns:

This task is very easy with Power Query. In the animated image below, I used the Power Query Add Column from Example feature to extract the position and name from the first column by simply giving Power Query an example.

Note: the image above uses Excel 2016/Office 365. For earlier versions of Excel you'll find Power Query is a free add-in (download it here) with its own tab on the ribbon:

In more complex data sets you may need to give Power Query two or three examples before it can correctly detect the pattern. But that’s way easier than writing the formulas yourself.

What’s also nice about this is you can see the formula Power Query has written for you In the Power Query Add Column from Example screen below. So, it’s a great way to learn the Power Query formula language.

Not Impressed?

What…you’re not that impressed with the Power Query Add Column From Example feature? Wow, tough crowd. Ok, let me show you another example. Below is some sales data by month for various regions. I want to calculate a rough margin of 10% and then add a column with some comments:

I think you’ll agree that it’s quite amazing.

Ok, I know the 10% calculation isn’t that difficult to do in Excel, but it’s more efficient to shift formulas like this to Power Query because they are only calculated upon refresh of your data. Likewise, IF and VLOOKUP type formulas are much more efficient in Power Query.

Power Query Add Column from Example isn’t limited to the examples shown here. You can perform many different transformations, including a plethora relating to dates.

Just take a look at the screenshot below; you can see I typed ‘week’ into the example field in Column1 and I'm presented with a list of week related information I can extract from the date field, including the name of the weekday, the week number and more as shown in the intellisense list below:

Loading the Data

When you’re finished adding columns and performing other data cleaning tasks in Power Query you can ‘Close and Load’ the data to various locations, as shown in the Excel 2016/Office 365 dialog box below:

Note: The Close and Load To dialog box may have less options in earlier versions of Excel.

Updating Queries

You’re probably wondering what happens when you change or add new data to your original (blue) table. For example, I’ve added a new employee to the table in row 11 and changed the position for Anne Smith on row 7:

And now all I need to do is right-click the (green) output Table of the query and click Refresh. Power Query grabs the updates in the blue table and adds the Position and Name columns before loading it to the green table:

That’s two clicks! TWO CLICKS to update EVERYTHING. And it doesn’t matter how many transformations I make in Power Query. Updating queries is always as simple as two clicks.

Because just like a macro it is reusable again and again.

You can even set the refresh to happen automatically if you want…but you don’t want to make yourself completely redundant 😉

Data From Other Sources

In these examples my data is in a Table in the Excel file, but Power Query can get data from a vast range of sources including CSV and text files, other Excel files, databases and more. The image below shows just some of the locations you can get data from:

Power Query Free Webinar + Course 20% Off

Everyone should learn Power Query because it simplifies and automates repetitive and labor intensive tasks that all Excel users perform. OK, maybe not everyone, but I'd estimate 95% of us. Just yesterday I got an email from one of my Power Query course members saying how she used Power Query to consolidate 4 trial balance reports, saving a load of time and it has made her look like an expert!

If you'd like to learn Power Query, please take a moment to check out my Power Query course Tip: get more than one course and take 20% off them too*.

Or for accountants and other professionals who require CPE credits my course is also available through Excel University with 12 CPE credits.

*Additional 20% off other courses applies to courses purchased through My Online Training Hub.

Workbook Download

Please Share

If you liked this please click the buttons below to share.