Clean data in Power Query to improve efficiency (part 1)

Have you ever pulled in data from various places to restructure it in Excel? Have you found yourself repeating this long, manual process every month when new data is added? The solution is to use Power Query. This blog is the first in a series of four. We will show you what Power Query can do to save you lots of time and help you become more productive. Let’s see how we can clean data in Power Query to improve efficiency (part 1).

What is Power Query and why is it useful?

Power Query is an app which transforms data sourced from many different locations before it is loaded into Excel. It works by making a connection back to the original data source. This means that whenever changes are made, they are saved as ‘Applied Steps’. These are stored in memory. The benefit is that a simple refresh in Excel is all that is needed to update any changes to the original data.

How to use Power Query
  1. In Excel, open a dataset with a column structure that needs some cleaning. In the example below, the ‘Full Name’ field has text entries where unwanted spaces need to be removed e.g. ‘ Anna Brown’

  1. Ideally the data needs to be a table before launching Power Query. To create a table, select a cell within the dataset and go to HOME > FORMAT AS TABLE. Select a coloured thumbnail and click OK in the ‘Create Table’ box
  2. Connect to the source data: DATA > FROM TABLE/RANGE. (NB. For any data that is external to Excel, go to DATA > GET DATA and browse to the specific data source)
  3. The following window appears:

  1. Note the ‘Applied Steps’ section on the right that will expand to include further steps
  2. Now select the ‘Full Name’ field, right click, and go to TRANSFORM > TRIM.

Right click on the ‘Extract Numbers’ field and go to SPLIT COLUMN > BY NUMBER OF CHARACTERS:

  1. Enter ‘2’ for ‘Number of Characters’ and select the radio button to split: ‘Once, as far right as possible’. Click OK
  2. Double click on each of the split columns and enter ‘Region’ and ID_no.

Now apply the changes back to Excel:

  1. In the Home tab, go to CLOSE & LOAD > CLOSE & LOAD TO > TABLE. Click OK to load the table to a new sheet
  2. Now in a new row under ‘Full Name’ add ‘ Mike Orange’ – with 2 leading spaces and under ‘Extract Numbers add ‘North19’
  3. Finally in the new sheet, go to QUERY > REFRESH and check the new data has changed

Power Query in Excel can connect to a vast range of external sources as well as within Excel itself. These include other Excel workbooks, financial systems, databases, websites, SharePoint, and many others.


Power Query is a powerful and extremely useful tool. It allows you to repeat routine cleaning tasks on your datasets without having to do it all manually. This tool will therefore help you become more efficient in the way you manage data which ultimately increases profitability. This concludes ‘Clean data in Power Query to improve efficiency (part 1)’.

For course details on our Power Query course at STL, please click on the link below:

Check out this useful article on data trends:












Telephone Techniques – How to use your voice effectively

The words we say make up only 30% of our communication. So we need to think about how we say them. To ensure that we are communicating effectively on the telephone, therefore, we must make the most of the 30% we have. Read on for four areas to focus on to improve your telephone technique.


This is the way that we use expression in our voice to convey a message. It covers all areas of non-verbal communication, and the four key areas below will help you to ensure that you are getting your message across as intended.


We express our emotions through our tone of voice. For example, if you think of times where you express happiness, excitement, frustration, confusion, or anger. On the telephone we need to be careful to tone down the negative emotions while exaggerating the positive ones. Even though the listener can’t see your face, your facial expressions will reflect in your voice. Try to remember, ‘smile when you dial,’ it really does work.



Speed is extremely important if we want to be easily understood. Often the temptation is to speak quickly so that we can end the call. However, speaking slowly and clearly will ensure that the listener is able to really hear you and you won’t have to repeat yourself. When we communicate face to face, we focus on the other person. On the telephone, the listener will be easily distracted. Therefore, you should try to keep sentences short and to the point. If you waffle, you could easily lose the person on the other end of the phone.


We need to manage our volume carefully. If we speak to quietly, we won’t be heard. This is especially true if there is background noise. If we are too loud, we can sound aggressive. When we focus on speaking at a neutral volume, we can naturally remain calm. Even if the other person is raising their voice.



If you speak with high energy, you can create a sense of excitement, urgency, and positivity with your voice. By contrast, if you speak with low energy, you will come across as boring, dull, and uninterested. The message you are conveying becomes irrelevant. You can naturally increase the energy in your voice if you walk around whilst you talk. However, if you sit or even slouch at a desk, the opposite happens!


Think about your paralanguage next time you are making an important call and see how these tips can improve the outcome. You can be more effective in sales, customer service or many other areas!