How to match comma separated cells values in two different columns and return missing values

[Kagliostro]

Hi Everyone,

new to Power BI and to DAX code and I need to perform a match between the content of two columns. Each column have a list of names that are separated by "," (comma) and I need the missing ones as the output in a new colums. The columns are part of the same table.

Column A	Column B	Missing
John Smith, Giuseppe Rossi, John Travolta	John Smith, Giuseppe Rossi	John Travolta
Clint Eastwood, Sergio Leone, Wild Bill Hickok	Wild Bill Hickok	Clint Eastwood, Sergio Leone

[Greg_Deckler · Accepted solution]

@Kagliostro Try this, PBIX is attached.

Column = 
    VAR __PathA = SUBSTITUTE( [Column A], ",", "|" )
    VAR __PathB = SUBSTITUTE( [Column B], ",", "|" )
    VAR __CountA = LEN( [Column A] ) - LEN( SUBSTITUTE( [Column A], ",", "" ) ) + 1
    VAR __CountB = LEN( [Column B] ) - LEN( SUBSTITUTE( [Column B], ",", "" ) ) + 1
    VAR __TableA = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __CountA ),
            "__Value", PATHITEM( __PathA, [Value] )
        )
    VAR __TableB = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __CountB ),
            "__Value", PATHITEM( __PathB, [Value] )
        )
    VAR __Missing = EXCEPT( __TableA, __TableB )
    VAR __Result = CONCATENATEX( __Missing, [__Value], ", " )
RETURN
    __Result

[Kagliostro]

Hello,

thak you for the foundmental help you gave me. I have a different question on the same script you provided.

Each row of Column  A may contain a different number of names while the script would work well on for those containing one name. For example, to make it work with column A having 3 names I have to change the Generateseries from 1 to 3 bunt won't work well for other rows.

in other words, could be possible to keep in account that the number of name s in column A and B can vary along the table?

[Kagliostro]

Thank you for your help. I would never be able to achieve that. It seems to work very well, so I am going to set the status as solved.