Forum Discussion
Extracting substrings: FIND throwing error despite being skipped by IF Statement
Hi all!
I'm trying to extract a column containing information (in this example: the numbers) from a column which looks something like this:
| INFORMATION01 |
| INFORMATION02 |
| ABCDEFG Info: 03 HIJKLMNOPQR |
| I felt like putting something else here. Info: 04 And some more text! |
| This row contains a typo. Ifo: 05 Lorem ipsum dolor |
My general idea is to create a DAX statement along the lines of:
"IF(CONTAINSSTRING(Table[column], "INFORMATION"), RIGHT(Table[column], 2),
IF(CONTAINSSTRING(Table[column], "Info: "), MID(Table[column], FIND("Info: ", Table[column])+7, 2), "Info not found")
Unfortunately, this causes the FIND function to throw an error for the whole column because not all rows contain "Info" and therefore, it could not return anything in those rows even if it wouldn't have to because CONTAINSSTRING would return false. Is there some way around this? (Eventually, I'd like to also account for typos with another IF-Statement)
Hi PowerBINewbie7 ,
You can update the formula of calculated column as below:
Column = VAR _information = SEARCH ( "INFORMATION", 'Table'[Text], 1, 0 ) VAR _info = SEARCH ( "Info: ", 'Table'[Text], 1, 0 ) VAR _info1 = SEARCH ( "Ifo: ", 'Table'[Text], 1, 0 ) RETURN SWITCH ( TRUE (), _information > 0, RIGHT ( 'Table'[Text], 2 ), _info > 0, MID ( 'Table'[Text], VALUE ( _info ) + 6, 2 ), _info1 > 1, MID ( 'Table'[Text], VALUE ( _info1 ) + 5, 2 ), "Info not found" )Best Regards
8 Replies
Hi PowerBINewbie7,
could you please share a screenshot of the error message? I cannot reproduce the error.One thing to mention besides that: If your tabele name really is "Table", you'll have to put it like this: 'Table'[column], because "Table" is a reserved word. Table[column] would return an error.
Hi timalbers,
The error message is just this:
with the column displaying "#ERROR" in each row.
My table is not actually named table, so that can't be the problem.
I've also tried replacing the MID-FIND part with just "Info found" and that works perfectly.
Ok this is strange.. I just simply copied your DAX logic and I am not able to reproduce the error.
Yup, my bad! I did not try my example code on this example, because I assumed the error would occur as it does in the table I'm actually working on... Sorry for wasting your time! I'll go back to figuring out what makes the table I'm working on different from my example.
Not a problem at all
If there are still issues, just come back to this thread.
I will try to help you best as I can
