Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Extract document number from a string using a list from another table as reference

Preferably as a DAX calculated column, I am looking for a way of extracting a document number from a string of text that exists in a column of one table whenever the starting values from a list that ...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi Anonymous ,

    Pls adjust to the below:

    Doc Number2 =
VAR Table_ =
    MAXX (
        FILTER ( Table2, SEARCH ( Table2[Column2], Table1[Column1],, 0 ) > 0 ),
        Table2[Column2]
    )
RETURN
    VAR Start_p =
        SEARCH ( Table_, Table1[Column1], 1 )
    VAR End_p =
        IFERROR (
            IFERROR (
                IFERROR (
                    SEARCH ( ".", Table1[Column1], Start_p ),
                    SEARCH ( " ", Table1[Column1], Start_p )
                ),
                SEARCH ( ",", Table1[Column1], Start_p )
            ),
            LEN ( Table1[Column1] ) + 1
        )
    VAR End_p2 =
        IFERROR (
            IFERROR (
                IFERROR (
                    SEARCH ( " ", Table1[Column1], Start_p ),
                    SEARCH ( ".", Table1[Column1], Start_p )
                ),
                SEARCH ( ",", Table1[Column1], Start_p )
            ),
            LEN ( Table1[Column1] ) + 1
        )
    VAR End_p3 =
        IF ( End_p < End_p2, End_p, End_p2 )
    RETURN
        IF (
            Table_ <> BLANK (),
            MID ( Table1[Column1], Start_p, End_p3 - Start_p ),
            ""
        )

     

    Output result:

     

    Best Regards

    Lucien

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you, Lucien.

 

Working on adjusting to this. It seems to work great in my model with test data but when I use it in my actual model I get the following error:

 

"The search Text provided to function 'SEARCH' could not be found in the given text."

 

At first I thought this might have been due to not all cells having data (some blank)... I tried changing the end to a SWITCH function instead of an IF function so that if a cell is blank just to leave it blank but that didn't work. Any thoughts here? I've tried a few tweaks to get around this but nothing I do seems to be working.

 

Thank you

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Got it! The error goes away if I nest the Start-p VAR into an ISERROR function and just run it again if the cell is blank. This removes the error and column is now working as expected!

 

 

 

VAR Start_p = 
IF(
ISERROR(
SEARCH(SEARCH ( Table_, Table1[Column1], 1 ), BLANK(),SEARCH(Table_, NL_Posted_Nominal_Trans[WO Narrative], 1))