Power Query: Splitting Up is Not so Hard to Do
5 July 2017
Welcome to our Power Query blog. Today I look at dividing a full name column into first and last names.
This is a fairly common scenario. I have a column which is made up of several pieces of data I want to extract, in this case a full name. As a programmer, I used to write chunks of code to do this, carefully stepping through the name until I reached the space. With Power Query, it’s much easier.
I will start from where I ended last week, with a file of expense data, which happens to include the full name of the employees. In order to show how this can be applied to any Excel data, I will load only the name to a query for this example.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image1.png/a492624984bc13575eb79a49fb911da1.jpg)
Since my data is already in a table, I take a copy and convert it to a range by using ‘Convert to Range’ option on the ‘TABLE TOOLS DESIGN’ tab (sorry for the shouting – I continue to use Excel 2013 for my examples!). Otherwise, Power Query will assume that the whole table should be loaded.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image2.png/df76f487e3be35bf585c57a5204c684e.jpg)
This gives me the range, which will no longer be connected to my ‘PQ_Names_in_with_Data’ query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image3.png/cc14fe9f204e3fe6a5f140656162291b.jpg)
There are several approaches I could take. I could make my ‘Employees’ column an Excel table and load that, but I don’t actually need to take that step in Excel. Instead, on the ‘POWER QUERY’ tab, I choose the ‘From Excel Range/Table’ option. If I don’t specify my data by selecting it first, then Power Query prompts me to confirm the data I want to use:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image4.png/ee95abb20be4f5abfb93b834d2d660ba.jpg)
So I can either specify cells D1:D18 on here or I can select those cells first and Power Query will automatically load them.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image5.png/640d9a8d838667e1496c7e3c5bfdfafb.jpg)
I now have my little table of data, and I am ready to split up the name.
On the ‘Home’ tab, there is an option to ‘Split Column’, and I can choose to do this ‘By Delimiter’ or ‘By Number of Characters’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image6.png/0c819ac776f65d97572d9d06baf75fc9.jpg)
I choose to split by delimiter, and the ‘Split Column by Delimiter’ screen appears. Power Query has correctly assumed ‘Space’ is my delimiter, but it’s worth taking a look at the other options:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image7.png/7ba87f75f1ec600d2e87aea35fdca147.jpg)
The most common options are explicitly listed and there is an option to customise my own delimiter for more complex data arrangements.
Also on the screen are a couple of other options:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image8.png/72a6e230d49cf5be3f15c1676fe07ee9.jpg)
I can choose how often to split my data using the delimiter – this would be useful if some of the names included a first and second name as well as a surname, in which case I could choose to keep the first two names together by only splitting at the right-most delimiter. There are also some advanced options which allow me to specify how many columns I want to see. If I were to choose one then I’d only see the first name, if I choose three, the last one would contain null data. I can also choose the quote style. If I had special characters in my columns I could also choose to use them as a delimiter.
I am happy that I want to use the space as my delimiter and I will split each time I see a space (since I know that my data only has one space anyway). I choose to output to two columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image9.png/473c7b9543edd3edaf63d7cc1bab65f9.jpg)
My two new columns are named ‘Employee.1’ and ‘Employee.2’, and I opt to ‘Close and Load’ which will load them to a new worksheet in my book. I rename them and insert my new columns into my original worksheet. Easy!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/july/pq-31-image10.png/c920c0c3ab031d1581b8b1b3e36e2460.jpg)
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!