Using DAX to Split Delimited Text into Columns

Note: This is a legacy blog post.

Imagine that you've loaded a table into Power Pivot and it has a column called 'Full Name' which contains comma delimited surname and first name values such as 'Smith,John'. You'd like to split 'Smith' and 'John' into separate columns.

Using DAX to Split Delimited Text into Columns Image 1

Ideally, we would split these values into separate columns before loading the data into Power Pivot. This can normally be done using Power Query, or with some SQL if the data is being loaded from a SQL database. That said, I've seen a handful of scenarios where this wasn't an option. I've seen solutions for splitting text into columns using the MID and FIND DAX functions like this but there is another solution that makes it easier to pick which delimited value to return. This solution uses a combination of the SUBSTITUE and PATHITEM DAX functions and is especially useful when there are more than 2 delimited values.

We can use the SUBSTITUE function to swap the comma delimiter with a pipe "|" and then the PATHITEM function to get one of the delimited text values. To get the surname, we can use the following DAX formula:

=PATHITEM(SUBSTITUTE(Person[Full Name], ",", "|"), 1)

Broken down into two steps, let's see how this solution handles the value 'Smith,John':

The SUBSTITUE function is changing the value 'Smith,John' to 'Smith|John'.

The PATHITEM function is checking 'Smith|John' for the first part of the pipe delimited text, which happens to be 'Smith'.

Using DAX to Split Delimited Text into Columns Image 2

To get the first name, we simply use the same formula but change the parameter of the PATHITEM function to a '2' so that it gets the second part of the pipe delimited text:

=PATHITEM(SUBSTITUTE(Person[Full Name], ",", "|"), 2)

Using DAX to Split Delimited Text into Columns Image 3

That's all there is to it. Now you know a quick and simple way to split out values from a delimited text column.