Power Query: Splendid Splitting
23 May 2018
Welcome to our Power Query blog. This week, I look at splitting data using a variety of delimiters.
I have some data for my ever-reliable fictional salespeople. As usual, the data is not in a format I would like!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image1.png/2a82f80345863514269add809d4e49e5.jpg)
Basically, I have a list of which salespeople are attending sales conferences and who is in charge at each one. Naturally, I’d like to put this data into a more useful table. What I would prefer is a list of pairings. I need to make sure I have a way of identifying which data is my sales conference location, which I why I have put ‘Location:’ before each city. I start by creating a query ‘From Table’ in the ‘Get and Transform’ section on the ‘Data’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image2.png/4a98901f9b8c71431eb24e4c73dcd15f.jpg)
I want to convert this data into a list of pairings, and the best way for me to do this is to tell Power Query what the delimiter is. However, I don’t want to actually split the column up at this point, so I won’t be using the ‘Split Column’ on the UI (user interface). Instead, I will be using some M code. The function I am using is
Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as table
I have a similar step already created for me to change the type (shown on the previous screenshot), so I can work out what my function needs to be.
= Table.TransformColumns(#”Changed Type”, {{"Sales Conferences", Splitter.SplitTextBy Delimiter(“, “), type text}})
This converts each of my column entries into a list as shown below:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image3.png/5abb360a085a50cb2a491c806c5f8a1c.jpg)
Now I need to extend this so that I have a long list, with all my lists contained within. In order to do this, I convert my table to a list in the ‘Transform’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image4.png/dc40d91c7e60fda4b2d0e7fe88c51710.jpg)
My data is now a list of lists, and I need to convert it back to a table so that I can have one long list. This is made possible by the ability to expand the data, as I will show shortly.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image5.png/21d9168816eee052e26cc4b40f2ea19d.jpg)
I don’t need a delimiter for this part, so I take the defaults.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image6.png/ffd7b6b34160b68aa3ffa1d50ed3aecd.jpg)
Now I have my table again, but I have the option to expand my columns, allowing me to view my separated data in one long column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image7.png/e38c5d7d192dd1db4f6caee6484ab545.jpg)
Once I ‘Expand to New Rows’ I can see all of my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image8.png/1b689f1095c5a11dfef95ebabf0471b8.jpg)
I need to pull out the sales conference locations and to do this I create a ‘Conditional Column’ from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image9.png/c2a238f03ec9486362162793d5407e10.jpg)
This new column will be populated with my location if the text contains ‘Location:’
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image10.png/7f14935c6c6cfe0af89a0cf5027b56a0.jpg)
I can right click on my new column and ‘Fill Down’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image11.png/568e93b7a73a061c60d6365fb4769957.jpg)
This is starting to look like my goal. Now I may swap my columns around and remove anything where Column1 contains ‘Location:’. I then rename Column1.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image12.png/a0b0511641d04f8771aa3fab05fef537.jpg)
I decide that the ‘Location:’ has been useful to identify the cities, but now it has to go! I split the column by delimiter (right click or use the transform menu).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image13.png/abfdb9e85ff9a8d0dc8e3e84994e8827.jpg)
I can now get rid of the Location.1 column and rename the other location column. I ‘Close & Load’ to Excel.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image14.png/81f909b4b3ee5e4b1a4c8745534b4f16.jpg)
Now to test my query. In my original Excel data, I try adding a new location (Leeds) and a new salesperson ‘Sam’, who will attend the conferences in Leeds and London.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image15.png/9777c1863ca0b2c75b78b8acaec33e4b.jpg)
After refreshing my query, the new salesperson Sam appears in both locations:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/05-may/77/pq-77-image16.png/fa823417477e4134c7e2a71eec329320.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!