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

v-luwang-msft's avatar
v-luwang-msft
Community Support
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