Forum Discussion

New Member

2 years ago

Solved

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

Need Help

Anonymous

2 years ago

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

Extracting substrings.pbix26 KB

timalbers

Skilled Sharer

2 years ago

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.