How to split caclulated column into rows using DAX?

[ashataltitude]

I have a calculated table that I created from 2 columns in another table. One of the columns in the calculated table is a calculated column. That calculated column is a concatenation of multiple values.

What I need to do is split that concatenated column and then create new rows from the values.

Here is what my table looks like:

Here is what I want to it look like:

However, to further complicate things, once split there will be duplicates as you can see highlighted. I will need to remove those so ultimately this should be my final table:

Since this is all calculated tables and columns I cant use Power Query so is there a way to do all this in DAX?

I did find this video that I think sort of gets me there so I feel it can be done somehow.

https://www.youtube.com/watch?v=j0A6CYg-BfA

I am trying to create this table to be able to link my Member table to my Lead table. Each of these tables is one row per unique Member or Lead ID. However, as you can see Members can have multiple Leads. Conversely Leads can also be connected to multuple Members so a table "mapping" all these connections seems to be the best way to go.

Any help is appreciated!

[ashataltitude]

Nevermind, I figured it all out..... in DAX.... by myself. Hooray me!

[v-yueyunzh-msft · Accepted solution]

Hi , @ashataltitude 

Thank you for your quick response and you solve it by yourself, you are genius !

I’m sorry i am mistaken in dax to split the column,as searched and test in my side. It can be realized in Power Query and also in dax.

Here are the steps i hope it can be helpful:

Power Query:

(1)This is my test data:

(2)We can use the Text.Split() and the List.Distinct() function to get the list we need, you can put this M code in the “Advanced Editor” in the “Home” Tab in Power Query Editor.

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRSk1NVUhTSFOK1QGJGAFFkpMVUlIUQOLJQACVMAZKJCYqgBFUyAQiBFEOFzWFiCYlJSnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Member ID" = _t, Leads = _t]),
    Custom1 = Table.TransformColumns(Source , {"Leads",(x)=>List.Distinct(Text.Split(x," "))    }),
    #"Expanded Leads" = Table.ExpandListColumn(Custom1, "Leads")
in
#"Expanded Leads"

(3)Then we can meet your need:

Dax in Power BI Desktop :

(1)This is my test data:

(2)We need to click “New Column” to create a calculated column:

Column = var _text = SUBSTITUTE([Leads] , " ","|")
return
PATHLENGTH(_text)

(3)Then we can click “New Table” and enter this and we can split the column:

Table 2 = var _max = MAX('Table'[Column])
var _index = GENERATESERIES(1,_max)
var _ct = CROSSJOIN( _index , 'Table')
var _ft = FILTER( _ct , [Value] <= [Column])
var _at = ADDCOLUMNS(_ft , "Leads_value" ,PATHITEM( SUBSTITUTE( [Leads] , " ","|") , [Value]) )
return
DISTINCT( SELECTCOLUMNS( _at , "Member ID" , [Member ID] , "Leads" , [Leads_value]))

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft]

Hi , @ashataltitude 

According to your description, you want to split the column in the calculated table. Right?

First of all, because it is a calculated table, you can't operate in Power Query, in DAX, if you want to split the column and the length of the corresponding value is inconsistent and the number is indeterminate.
In DAX, we can basically only simply use text functions such as FIND() to find the position of the space, and get the specified value, there is no way to return the List directly like in Power Query.
For your needs, this is a data preprocessing operation, if possible we recommend that you process it in Power Query, which will make the problem simpler.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly