Forum Discussion

Kagliostro's avatar
Kagliostro
Frequent Visitor
2 years ago
Solved

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

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 AColumn BMissing
John Smith, Giuseppe Rossi, John TravoltaJohn Smith, Giuseppe RossiJohn Travolta
Clint Eastwood, Sergio Leone, Wild Bill HickokWild Bill HickokClint Eastwood, Sergio Leone
   
   
Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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's avatar
      Kagliostro
      Frequent Visitor
      2 years ago

      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.

    • Kagliostro's avatar
      Kagliostro
      Frequent Visitor
      1 year ago

      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?