Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
wegemmell
Helper III
Helper III

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

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1669627289322.png

 

Best Regards

Lucien

View solution in original post

9 REPLIES 9
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1669627289322.png

 

Best Regards

Lucien

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

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
Solution Sage
Solution Sage

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

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!

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
Solution Sage
Solution Sage

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

Bifinity_75_0-1669066904613.png

 

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

I hope works for you!, best regards

Bifinity_75
Solution Sage
Solution Sage

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:

 

Bifinity_75_0-1669058125306.png

 

Best regards

Thank you, @Bifinity_75 !

 

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

 

wegemmell_0-1669063401274.png

 

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.

 

wegemmell_1-1669063509514.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.