POWERBI, DAX : concatenate strings, split, and keep substrings only once

Question

I try to do the following:

I have a column with strings, each can have several substrings separated by a delimiter ":"
I need to Concatenate the column strings (I do a Filter here to keep only interresting lines)
Then split according to the delimiter ":"
keep the substrings only once, if they are repeated.

Example:

ColumnHeader
AA:BB:CC
BB:DD
DD:AA:EE
EE:AA:DD:BB
BB:EE
...

Expected result would be a unique string:

"AA:BB:CC:DD:EE"

How would you do this in DAX to fill a new column ?

I expected to find for/while loops in DAX like in Python ... but failed.

I Tried this:

List =
VAR SIn = ""
VAR SOut = ""
VAR Cursor = 0
VAR SList =
        CONCATENATEX(
            FILTER(ATable, ATable[Name] = CTable[Name]),
            [ColumnHeader],
            ":")
VAR pos1 = FIND(":", SList, Cursor, len(SList))
VAR pos2 = FIND(":", SList, pos1, len(SList))
VAR elem = TRIM(MID(SList, pos1+1, pos2-pos1))

// following is not good but is what I would like to do:
VAR SOut = CONCATENATE(SOut, elem)
VAR SList = MID(SList, pos2, len(SList)-pos2)
VAR Cursor = pos2

// I need to loop ... but how ? ... as no for/while loops are possibles ?

Thanks for your help.

=====================================

I manage to tackle this thanks to the answers below.

I will still give a bigger data set for a better understanding of the global problem:

I have 2 tables:

TABLE_BY_ELEMENT            
KEY GROUP   LIST        KEY_DATA
1   G1      AA:BB:FF    11
2   G1      CC:AA       22
3   G1      FF:DD:AA    33
4   G1      CC:DD:AA    44
5   G2      CC:FF:GG    55
6   G2      BB:AA       66
            
            
TABLE_BY_GROUP          
GROUP   GROUP_DATA      
G1      1111        
G2      2222

And I want to view the data like this:

RESULT_BY_GROUP     
GROUP   GROUP_DATA  NewList
G1      111         AA:BB:FF:CC:DD
G2      222         CC:FF:GG:BB:AA

and also:

RESULT_ELEMENT      
KEY LIST        KEY_DATA
1   AA:BB:FF    11
2   CC:AA       22
3   FF:DD:AA    33
4   CC:DD:AA    44
5   CC:FF:GG    55
6   BB:AA       66

I hope is is easier to understand with this.

user_765226 · Answer

This isn't something DAX is suited for well. If you need to use DAX to make it into a dynamic measure, then you'll probably need to reshape your data to be more usable. For example,

ID   ColumnHeader
1    AA
1    BB
1    CC
2    BB
2    DD
3    DD
3    AA
3    EE
...

You can do this split in the query editor using the Split Column > By Delimiter tool and choosing to split on the colon and expand into rows.

Once it's in this more usable format, you can work with it in DAX like this:

List = CONCATENATEX( VALUES('Table'[ColumnHeader]), 'Table'[ColumnHeader], ":" )

Borrowing logic from here, it's possible to do this purely in DAX, but I don't recommend this route.

List =
VAR LongString =
    CONCATENATEX ( VALUES ( 'Table1'[ColumnHeader] ), Table1[ColumnHeader], ":" )
VAR StringToPath =
    SUBSTITUTE ( LongString, ":", "|" )
VAR PathToTable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, LEN ( StringToPath ) ),
        "Item", PATHITEM ( StringToPath, [Value] )
    )
VAR GroupItems =
    FILTER (
        SUMMARIZE ( PathToTable, [Item] ),
        NOT ISBLANK ( [Item] )
    )
RETURN
    CONCATENATEX ( GroupItems, [Item], ":" )

user_3652345 · Answer

Let your table looks like below-

Now try this below Advance Editor code in the Power Query Editor-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnS0cnKycnZWitWJVgKyXFzALBcXK6CMqyuY4+oK4gCFnJxgykAysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnHeader = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnHeader", type text}}),
    
    
    //--NEW STEPS STARTS FROM HERE
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "ColumnHeader"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "ColumnHeader", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"ColumnHeader.1", "ColumnHeader.2", "ColumnHeader.3", "ColumnHeader.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ColumnHeader.1", type text}, {"ColumnHeader.2", type text}, {"ColumnHeader.3", type text}, {"ColumnHeader.4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute", "Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Value", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Index1",{"Index", "Value"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns1", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns1", {{"Index", type text}}, "en-US")[Index]), "Index", "Value", List.Max),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3", "4", "5"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Here is the final output-

user_3652345 · Answer

Here is code from Power Query Editor considering GROUP BY-

Create a new table RESULT_BY_GROUP with this below code-

let
    Source = TABLE_BY_ELEMENT,
    #"Removed Columns" = Table.RemoveColumns(Source,{"KEY", "KEY_DATA"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "LIST", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"LIST.1", "LIST.2", "LIST.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"LIST.1", type text}, {"LIST.2", type text}, {"LIST.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"GROUP"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns1"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"GROUP", Order.Ascending}, {"Value", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"GROUP"}, {{"all", each _, type table [GROUP=nullable text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewList", each [all][Value]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"NewList", each Text.Combine(List.Transform(List.Sort(_), Text.From), ":"), type text}),
    #"Removed Columns2" = Table.RemoveColumns(#"Extracted Values",{"all"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns2", {"GROUP"}, TABLE_BY_GROUP, {"GROUP"}, "TABLE_BY_GROUP", JoinKind.LeftOuter),
    #"Expanded TABLE_BY_GROUP" = Table.ExpandTableColumn(#"Merged Queries", "TABLE_BY_GROUP", {"GROUP_DATA      "}, {"TABLE_BY_GROUP.GROUP_DATA      "}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded TABLE_BY_GROUP",{{"TABLE_BY_GROUP.GROUP_DATA      ", "GROUP_DATA"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"GROUP", type text}, {"NewList", type text}, {"GROUP_DATA", Int64.Type}})
in
    #"Changed Type1"

Here is the final output-

You can easily visualize your second requirement for table RESULT_ELEMENT using your base table TABLE_BY_ELEMENT

POWERBI, DAX : concatenate strings, split, and keep substrings only once

3 Answers