Forum Discussion
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!
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
9 Replies
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
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.
Hi Anonymous , 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:
Here are some of the issues I am seeing:
- When there is just an expected result in the search column (Table1), the last digit of the document number is being removed.
- When there are expected results that have extension on them separated by a "." the entire document number with the extension is still being retreived.
- 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!
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
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))
Hi Anonymous !, 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
