Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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 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
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?
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.
Check out the July 2025 Power BI update to learn about new features.