Procurement Data: Connecting tables after removing text from column

[ngupta]

I have some procurement data that i want to analyze and I need some help.

Data includes two tables one has  Tender # and Open_date and other has Contract # and Execution date. When a tender is opened it takes some time to execute the contract. the # stays the same, just the word TND is replaced by CON.

Table 1: (approx 1000 rows)

Col 1:TND                Col2 :Open date :
TND0018987          Jun 7, 2017

Table 2: (approx 7000 rows)
col 1:contract          Col2 exe_date
CON 0018897          Aug 1, 2017

Objective : I want to connect tender from table 1 to contract in table 2, and calculate delay in days between open and execute date.

Two Problems I face:

1. Table 2 has Contract names such as IMB1456, 456545, 4567ATB, CE1245. where there are numbers before or after letters. This in addtion to CON00118897 type of names. I am only interested in CON# as I am only analyzing TND to CON delay.  Is there a way to extract only numbers out of the name?

if not, Can i create a separate table that only has rows that have format CON#  in the Contract column and ignore all the other contract names. Will I be able to do everything with this table.  (When I created a table I did not see it in query editor )

2. In Table 2, I removed alphabets TND from TND###### and in Table 1, i removed CON from all rows in column with contracts. I assumed that a 1: 1 relationship can be now formed between TND and CON. converting 001234  text to 1234 integer did not work as there were several rows that had alphanumeric names.  One to many (one to one cardinality was not acceptable) from tender to contract was working but  "datediff(date1 (exe_date),date2 (opendate),day) to calculate the delay by creating a column does not work.  I get this error :
 A single value for column 'TENDER_OPEN_DATE' in table 'TENDER_DIMS' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Any suggestions??

to calculate date difference when one to many relationship has been used??

or any other way to achieve my objective?

[v-chuncz-msft · Accepted solution]

@ngupta,

You may use LOOKUPVALUE Function to add a calculated column.

Column =
LOOKUPVALUE (
    Table2[exe_date],
    Table2[contract], REPLACE ( Table1[TND], 1, 3, "CON" )
)