cancel
Showing results for
Did you mean:

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

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
Community Support

Hi @wegemmell ,

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

9 REPLIES 9
Community Support

Hi @wegemmell ,

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

Helper III

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

Helper III

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

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

Helper III

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!

Helper III

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

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

I hope works for you!, best regards

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

Column

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

Column2

 ABC- DEF- GHI- JKL- MNO-

The result:

Best regards

Helper III

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors