Power BI Split Column By Delimiters In DAX- Enterprise DNA

Power BI Split Column By Delimiters In DAX

No comments

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.

Sample Problem

This is the sample problem in Power BI.

Sample Problem

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.

Sample Problem

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. 

Steps To Split Columns Using Delimiters

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. 

Virtual Tables

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

Finding Useful Power BI Split Column Tools In SQLBI DAX Guide

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

Finding Useful Power BI Split Column Tools In SQLBI DAX Guide

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.

Finding Useful Power BI Split Column Tools In SQLBI DAX Guide

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

Finding Useful Power BI Split Column Tools In SQLBI DAX Guide

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. 

Power BI Split Column In DAX

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

Power BI Split Column In DAX

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

Power BI Split Column In DAX

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. 

Power BI Split Column In DAX

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. 

Power BI Split Column In DAX

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. 

Power BI Split Column In DAX

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

Power BI Split Column In DAX

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

Power BI Split Column In DAX

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

Power BI Split Column In DAX

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. 

Power BI Split Column In DAX

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

Power BI Split Column In DAX

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.

Power BI Split Column In DAX

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. 

Power BI Split Column In DAX

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. 

Processing The Split Columns Using The Tabular Editor 3

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

Processing The Split Columns Using The Tabular Editor 3

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. 

Processing The Split Columns Using The Tabular Editor 3

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

Processing The Split Columns Using The Tabular Editor 3

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

Processing The Split Columns Using The Tabular Editor 3

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

Processing The Split Columns Using The Tabular Editor 3

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. 

Processing The Split Columns Using The Tabular Editor 3

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. 

Processing The Split Columns Using The Tabular Editor 3

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.

Processing The Split Columns Using The Tabular Editor 3

***** Related Links *****
Power Automate Split Function And Arrays
Power BI Column Split Power Query Tutorial: How To Split Multi-Lined Excel Cells In Power BI
Create A Delimited List In Power Query – Query Editor Tutorial

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.

Enterprise DNA Power BI On-Demand

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.