In this video we look in depth at one particular element of formatting; capitalisation
In standard Excel, we can capitalise text in a certain way; either by elevating all text to capitals, lowering all the text to lower case or capitalising the first word of all words by using certain functions.
The UPPER function capitalises all letters in a word, while the LOWER function puts all letters in lower case. The PROPER function capitalises the first letter of each word.
PowerQuery has the same capabilities but at the single click of a button.
In the video to demonstrate this capitalisation ability, we look at extracting some names from email addresses and then capitalising the names correctly. All the names are in slightly different formats. Some have some capitals, others don’t and still others are completely capitalised.
How Can We Capitalise Words Correctly in PowerQuery?
Firstly select the columns that has the data to which you’d like to apply your formatting.
By now you should be used to the actions popping up in the Query pane and sure enough, there’s a new step added of Capitalized Each Word.
What is the ‘Extract’ option?
In the last lesson we looked at splitting data at certain points, at delimiters, in each cell in a column of data. The data in each cell would then be separated out into columns at each of those delimiters.
Extraction does a similar sort of thing. We give PowerQuery a delimiter and then we can ask PowerQuery to extract all the information before, after or between the delimiters. The rest of the information is then removed from the dataset. This is ideal when we just want a section of data from a cell, but don’t need all the information.
So how do we perform
How to use the Extract option
First of all, identify the column that has the data that we want to extract.
The Formula Bar – We don’t look at the Trim function in this lesson’s video but it’s a very useful function. Here’s why:
PowerQuery is very exact in it’s code. If you don’t have capitals in the right places or you have leading or trailing spaces in your dataset, this can cause problems and result in PowerQuery not doing what you expect. Take for example Merging which is a function we get to later in the course. Merging brings together two tables of information using a ‘key’, a piece of information that is common in both dataset. Using the ‘key’, PowerQuery can match the correct rows together, much like a VLOOKUP does. However if the ‘key’ column in one dataset has trailing spaces which aren’t in the key column of the matching dataset, then the Merge function won’t work. It’s quite difficult to pick out why a Merge function isn’t working properly, but the Trim function will eliminate any trailing spaces, which aren’t obvious from just looking at the dataset, which could be a principal reason.