Power BI Split Column By Delimiters In DAX

In today’s blog, you will learn how to use Power BI to split column by delimiters using DAX. This is something you would probably know how to do easily using Power Query, and that is generally the best way to do it.

But because of the dynamic nature of the problem and the specific considerations within the data model, there are situations where using DAX provides a better solution. You can watch the full video of this tutorial at the bottom of this blog.

Table of Contents

Sample Problem

This is the sample problem in Power BI.

You have a list of about 600 movie titles and you want to identify the most common words among them. To do that, you will use the parameter slider shown below to calculate the words from each title.

In the slider, you can set how many top words you want to return. For this tutorial, say you set it to five words.

Your next goal is to return every title that has at least one of the five words you selected. Doing so may sound simple, but that is a little trickier than it seems. So here are the steps to accomplish these goals.

Steps On How To Split Columns Using Delimiters

This section will teach you how to solve the given problem by splitting columns in DAX. The first step is to open the movie data by clicking the second icon on the left-hand side, then Film on the Fields Pane.

In the list of films that will show up, you need to think of ways to break the titles down into individual words.

One possible solution is to use two virtual tables as shown below. Table 1 is a one-column table of the top N words defined by the slider. The second table is a virtual column of the words in each movie title.

Then, using the two virtual tables, you can use the INTERSECT function to determine what words are common between them. Next, do COUNTROWS on that virtual table to know if that number is greater than or equal to one.

If the answer is yes, then at least one of the target words are in the movie title. And those are the titles we want to retain in the final result.

Finding Useful Power BI Split Column Tools In SQLBI DAX Guide

To think about how you can implement that, you may refer to the SQLBI DAX Guide by going to the External Tools then DAX Guide.

On this page, you can search functions by type by clicking Groups.

Under Functions, you may start with Text to see if there are any text functions similar to split columns by delimiters. That would make things quite easy, but unfortunately, there’s nothing that corresponds to that in DAX.

Instead, what you will use are the parent-child hierarchy functions that do the same thing as split by the delimiter.

Parent-Child Hierarchy Functions

The parent-child hierarchy functions or the PATH functions are typically used for hierarchical situations. Let’s say you need an HR analysis where you have the CEO of a firm, the director-level managers, branch chief-level managers, and so forth.

PATH functions can express this hierarchy in a particular way and traverse back and forth through it. This is the traditional use of the parent-child hierarchy functions, but you will learn that it also has some very useful properties for text manipulation and DAX.

Using it is a lot trickier than Power BI, but we hope to make the process easier by giving you a step-by-step solution. It is a useful technique to learn, and it exposes you to a family of DAX functions that are incredibly powerful.

Power BI Split Column In DAX

The parent-child hierarchy functions involve a number of steps, but they are worth taking a look at because these five functions are really powerful and flexible.

To start, go back to the table and click Film Path in the Fields Pane.

Create a film path using the SUBSTITUTE command as shown below.

What this command does is replace every space with a vertical bar character.

This is the same way paths have to be formatted in Power BI, so typically, this is done in Power Query. However, you did it this way to see the PATH commands, which are different from the M commands in Power Query.

The SUBSTITUTE function creates the path structure that you need, while the TRIM command gets rid of any leading or trailing spaces that could affect our results.

Your second calculated column is the Path Length. It is a straight-up command in the PATHLENGTH function that you apply to the film path. It will tell you how many items are on that path, so in this case, it is the number of words in the title.

The same command can be used to determine, for example, the number of people in the reporting chain. But this case is just a simple word count.

The next thing to do is go to the Max Path Index in the Fields pane. Then, create a calculated table that goes through the entire movie table, removes the filters, and calculates the maximum path length to know the longest title in the database.

Using the GENERATESERIES function, you can generate a series of indexes with one to the max path counting by ones.

This is what your table should look like. You will see in a minute why this is valuable.

For now, go back to Power BI and click Top Words in Film Path in the Fields Pane.

We will focus on this virtual table whose general solution was the Venn diagram shown earlier.

Let’s break it down to better understand. In the lines below, we used the virtual table for the top N words, as calculated below.

Then the split column by delimiter is calculated in the lines below.

Next, we applied the intersection of those two columns. If that intersection is greater than zero, we will give it a one or else, zero. Finally, we returned the result.

When working with complex virtual tables, the easiest way is to go into the Tabular Editor, where you can materialize these virtual tables in a DAX query. For that, you need to focus on the lines highlighted below.

So, copy this code and let it sit in our clipboard for the next steps.

Processing Power BI Split Columns Using The Tabular Editor 3

In the External Tools, click Tabular Editor 3. You may also use DAX Studio for this, but Tabular Editor is recommended because of its additional debug functions and other nice capabilities. For DAX queries, DAX Studio will work well too and it is totally free.

Back in the Tabular Editor, create a new DAX Query by clicking the paper icon in the ribbon. Type EVALUATE in line 1, paste the copied code, then press F5.

Processing The Split Columns Using The Tabular Editor 3

You will get a table with the Film Path and Max Path for the first two columns. For the third column, you will use the PATHITEM command and the Film Path to determine what the first word is for each increment of the path index, from one to nine.

This code returns the first word presented in the third column.

Try looking at a particular film by clicking the Filter icon in the Movies[Film Path] column title. For example, take one that is a little bit longer, say Captain America: The Winter Soldier.

In the lower part of the window, you can see that EVALUATE returned 1000 rows.

Get all rows and then go back and filter Movies[Film Path] for Captain America again.

Search for Captain America: The Winter Soldier. Check the appropriate box and click Close.

What it exactly does for each title is go through and apply the one through nine indexes, as seen in the second column, and then apply that to the PATHITEM command.

You can see that the first item is Captain, the second item is America, the third is The, the fourth is Winter, and the last item is Soldier. When you get from six to nine, it’s just blank because it has run past the number of words in that particular title.

What you have created is a split by delimiter column, which is exactly the same as the split by delimiter in Power Query.

In Power BI, you can see that the PATHITEM command that runs through the intersection and the IF statement. So, if you go back into your data set and open the Filters pane, you can see that the Top Word in Film is filtered so that it is equal to one.

This basically says that everything in the right table (Film) that has a word that is in the target list in the left table will be listed in the upper rows. On the other hand, the ones that are zero will be placed down.

Conclusion

In this blog, you learned how to split columns by delimiter in DAX. You will find this tool really helpful when your situation is very dynamic or your data model doesn’t support doing it in Power Query.

Additionally, the sample problem you worked on is a good illustration of the unusual power of the PATH commands and how you can use those to solve problems in non-traditional ways.