Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a list codes that are located in the same table , they are shown in two seperate columns.
I am trying to find a way to detect a match , when they are not exactly the same.
Example
CodeA CodeB
000123 123
678A 001/678A
HV/0189 0189
These should all be classed as a match as
123 is contained in CodeA
678A is contained in CodeB
0189 is contained Code A
Is there a formula that can help with this please
Solved! Go to Solution.
Hi,
Please try this column:
Column =
IF (
CONTAINSSTRING ( 'Table'[CodeB], 'Table'[CodeA] )
|| CONTAINSSTRING ( 'Table'[CodeA], 'Table'[CodeB] )
|| IF (
CONTAINSSTRING ( 'Table'[CodeB], "-" ),
CONTAINSSTRING (
'Table'[CodeA],
LEFT ( 'Table'[CodeB], FIND ( "-", 'Table'[CodeB], 1, 1 ) - 1 )
)
&& CONTAINSSTRING (
'Table'[CodeA],
RIGHT (
'Table'[CodeB],
LEN ( 'Table'[CodeB] ) - FIND ( "-", 'Table'[CodeB], 1, 1 )
)
)
),
"Match",
"No Match"
)
The result shows:
See my attached pbix file.
Best Regards,
Giotto
Hi,
Please try this column:
Column =
IF (
CONTAINSSTRING ( 'Table'[CodeB], 'Table'[CodeA] )
|| CONTAINSSTRING ( 'Table'[CodeA], 'Table'[CodeB] )
|| IF (
CONTAINSSTRING ( 'Table'[CodeB], "-" ),
CONTAINSSTRING (
'Table'[CodeA],
LEFT ( 'Table'[CodeB], FIND ( "-", 'Table'[CodeB], 1, 1 ) - 1 )
)
&& CONTAINSSTRING (
'Table'[CodeA],
RIGHT (
'Table'[CodeB],
LEN ( 'Table'[CodeB] ) - FIND ( "-", 'Table'[CodeB], 1, 1 )
)
)
),
"Match",
"No Match"
)
The result shows:
See my attached pbix file.
Best Regards,
Giotto
@Pandadev , Create a new column like
IF(SEARCH([CodeA],[CodeB],,0) >0 , [CodeA] & " is contained in CodeB",
if( SEARCH([CodeB],[CodeA],,0) >0 ,[CodeB] & " is contained in CodeA" , "No Match"))
Sure, use SEARCH or FIND. Column would be like:
Column =
IF(SEARCH([CodeA],[CodeB],,-1) <> -1 || SEARCH([CodeB],[CodeA],,-1) <> -1,"match","no match")
Thanks , that is nearly perfect.
Just a couple of codes that dont work with this method
TH1499 and TH-1499
TE 1191 AND TE-1191
which is caused by spaces , and ,/-
is there an easy way to remove these
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |