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

Bifinity_75's avatar
Bifinity_75
Solution Sage
3 years ago

Hi Anonymous , try this calculate column:

C_Column = 
VAR Table_ =
    FILTER (
        Table2,
        VAR v_ = Table2[Column2]       
RETURN       
        CONTAINSSTRING (Table1[Column], v_)
    )
        RETURN
VAR Start_p=SEARCH(Table_,Table1[Column],1)
VAR End_p=IFERROR(
            IFERROR(
                IFERROR(
                    SEARCH(" ",Table1[Column],Start_p),
                     SEARCH(".",Table1[Column],Start_p)),
            SEARCH(",",Table1[Column],Start_p)),
          LEN(Table1[Column]))
RETURN
MID(Table1[Column],Start_p,End_p-Start_p)

 

Your Table1 is this Table1:

Column

ABC-7450.4.1 
DEF-7665 1.2: 
INV-12345 GHI-5632.2.3 
JKL-45698 
MNO-10267.3.1 

 

Your Table2 is:

Column2

ABC-
DEF-
GHI-
JKL-

MNO-

 

The result:

 

 

Best regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you, Bifinity_75 !

     

    In my model I am getting the below error, but I will figure that part out.

     

     

    When I build a test model such as the one you created, I can't seem to replicate the same results you produced (see below). Any thoughts?

     

    Also, if there isn't a match from the list table found (see TEST row below), do you have a recommendation for leaving the result in the new column blank? Really appreciate your help here.