Sign in or start a free trial to avail of this feature.
12. Combining and Splitting Text Fields
Continuing our look at text functions, we will see how to combine and split columns of text data.
Combining and Splitting Text Fields (00:17)
The goal of this lesson is to combine and split text fields using DAX text functions.
Combining Text Fields (00:23)
We can combine text fields using the function CONCATENATE. This function takes two arguments, and combines them. The arguments can be references to a text column, or a specific text string, enclosed in double quotes. If we want to combine more than two strings, then we need to use nested CONCATENATE functions, which are similar to the nested IF functions from previous lessons.
Splitting Text Fields (01:35)
DAX does not contain a single function that allows you to split a text string. Instead, we need to identify the point at which the string will be split, then use LEFT or RIGHT to extract the characters before or after that point. For example, we have a coordinates column containing latitude and longitude values, separated by a comma and a space. We can use LEFT to identify the latitude, and RIGHT to identify the longitude.
To split a text field and identify characters from the start of the field, we use LEFT. To identify the number of characters to return, we use FIND. We use FIND to identify the point where the string will be split, for example the comma. We also subtract one so that the comma itself is not included in the string returned by LEFT.
To split a text field and identify characters from the end of the field, we use RIGHT. Identifying the number of characters to return is more complicated here. In our case, we use the LEN function to identify the length of the string, subtract the index of the point where the string will be split (which we identify using FIND), then subtract one to account for a space in the string which we do not want to return.
Combining and splitting text fields is a common task with real world datasets. While you can use the query editor to combine and split text fields without coding, there are also DAX formulas that you can use instead. In this final lesson of this course, we'll focus on combining and splitting text fields, using several different DAX functions.
First, we'll the concatenate function to join the latitude and longitude field.
To do this, we'll go to data view, navigate to the pharma data table, create a new column called coordinates, and enter the concatenate function.
We're going to combine the latitude and longitude fields, while also adding a comma and space between them, in order to format the output appropriately.
This means we're combining three text strings.
I'll paste in the formula used to accomplish this, and run through an explanation.
The concatenate function only accepts two arguments. So to combine three arguments, we need to nest another concatenate.
Therefore, we'll combine the latitude field with another concatenate, where we combine a comma and space with longitude. Note, that using the concatenate function to combine a large number of strings is possible, but it can lead to a messy formula.
Let's press enter to create the new column. We can see our latitude and longitude fields are now combined into a single column.
Let's move on and demonstrate splitting columns. Splitting columns using formulas is a bit trickier, as there is no split formula in DAX.
Instead we'll find the character where we want to split the string, and then extract the relevant portion of the string from before or after the split point.
Let's split the coordinates column back into longitude and latitude to see how this works.
Latitude will be everything to the left of the comma, while longitude is everything to the right.
We'll create a new column called split lat.
We'll then enter the LEFT function to find characters from the left of the coordinates column.
The number of characters will be determined by using the find function to find the comma symbol in the coordinates column.
We'll subtract one, so that the comma itself is not included in our extracted string.
We don't need to include the third and fourth arguments to find, as we know that every value in the coordinates column has a comma. So, there is no danger of the function returning an error. We'll press enter and see if the latitude has been successfully extracted. In order to extract the longitude, we'll create another column call split long.
This process is a bit more convoluted, so I'll paste in the formula and explain it.
In this case, we'll use the RIGHT formula to extract characters starting from the right end of the string. We'll, again, use the comma as the dividing point, but we can't just use the find function like we did last time.
We use the LEN function to find the length of the coordinate string, and then subtract the index where the comma is found using the find function.
Finally, we subtract one more to account for the space which follows the comma. We'll press enter to accept this formula, and create the new column.
As we can see, these split columns for latitude and longitude match the existing columns in our dataset.
This brings us to an end of our first course on DAX. This course is focused on introducing the fundamental concepts of DAX, such as calculated columns and measures, as well as the most common functions you're likely to use. In the next course, we'll look at some more advanced DAX applications like time intelligence, as well as concepts like filters and context.