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

[wegemmell]

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 are in a column in another table are found. The end of the document number can either be a period or a space, whichever occurs first.

Example column List from one table to reference (Table A):
ABC-
DEF-
GHI-
JKL-
MNO-

Example column text from another table (Table B):
ABC-7450.4.1 #1: Other text here.
DEF-7665 1.2: Other text here.
INV-12345 GHI-5632.2.3
JKL-45698
MNO-10267.3.1 #3: Other text here.

Expected Results in a new column in table B:
ABC-7450
DEF-7665
GHI-5632
JKL-45698
MNO-10267

I've done a bit of research on other similar examples but haven't had any luck getting this one nailed down. All help is greatly appreciated!

[v-luwang-msft · Accepted solution]

Hi @wegemmell ,

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

[wegemmell]

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

[wegemmell]

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))

[Bifinity_75]

Hi @wegemmell , If you have problems with the previous formula, try this one:

C_Column = 
VAR Table_ =
    maxx(filter(Table2 , search(Table2[Column2],Table1[Column],,0)>0),Table2[Column2])
        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
IF(Table_<>BLANK(),
MID(Table1[Column],Start_p,End_p-Start_p),"")

Best regards

[wegemmell]

Hi, @Bifinity_75 

First, thank you for all your help and guidance here. Sorry to bother again. I have been playing with this formula and testing different functions within, but I keep getting the same issues...

Below is a document I created with a sample of actual data I am working with:

Example Document with Data

Here are some of the issues I am seeing:

1. When there is just an expected result in the search column (Table1), the last digit of the document number is being removed.
2. When there are expected results that have extension on them separated by a "." the entire document number with the extension is still being retreived.
3. In my full model when I run this DAX column I get the error, "The search Text provided to function 'SEARCH' could not be found in the given text." but that's something I can contonue to play with and figure out. 

Any input you can provide is greatly appreciated!

[wegemmell]

As an update, I got around the length issue by simply adding a "+1" to the end of the End_p VAR, which seems to have worked well, "LEN(Table1[Column1])+1)"

Still trying to solve for #2 above. I thought of maybe swapping to a switch function for the searches instead but that failed.

[Bifinity_75]

Hi @wegemmell !, for leaving the result in the new column blank, add this lines to the final of the calculate column:

IF(Table_<>BLANK(),
MID(Table1[Column],Start_p,End_p-Start_p),"")

If the formula does not work for you, you can send me the file privately.

I hope works for you!, best regards

[Bifinity_75]

Hi @wegemmell , 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

[wegemmell]

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.