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 missi...
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

     

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
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?