Power Query: Replace to Relocate – Simpler than Brexit

Welcome to our Power Query blog. Today I look at a simple manipulation of table data.

Whilst I was creating data to use for my training and blogs on Power Query, I spent time manipulating my data in a Microsoft Access database. It is however possible to do much of this transforming in Power Query and today I will look at a simple example. I have a table ‘Employees’ which sits in my database, and I will extract it to Power Query in order to make some changes:

I have a list of employees, and they are each allocated an area and a sales territory. However, I am thinking ahead here, and thanks to 52% of my fellow UK citizens, the UK will soon no longer be in the EU. I need to change the data to show UK instead of EU in the sales territory. I begin by editing my query.

With my SALES_TERRITORY_KEY column highlighted, I go to the ‘Transform’ Menu and choose to ‘Replace Values’:

I enter my values to be replaced ‘EU’ and my new value ‘UK’. I use the ‘Advanced Options’ to match the entire cell to make sure I don’t inadvertently pick up some other region with an ‘EU’ in it. I’d quite like some conditional logic in this box in future updates of Power Query but for now, it’s an absolute replacement of all values. I press ‘OK’ and…

Instantaneously, the UK is out of the EU (it’s a shame it won’t be this simple outside of Power Query!).

I choose to close and load my query:

I can put my updated employee data into my Access database if I choose. I create a new empty copy of my ‘Employees’ table called ‘Employees Updated by PQ’ and paste my data in.

My data is now updated and ready to use.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!