cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Procurement Data: Connecting tables after removing text from column

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?image.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Procurement Data: Connecting tables after removing text from column

@ngupta,

 

You may use LOOKUPVALUE Function to add a calculated column.

Column =
LOOKUPVALUE (
    Table2[exe_date],
    Table2[contract], REPLACE ( Table1[TND], 1, 3, "CON" )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Procurement Data: Connecting tables after removing text from column

@ngupta,

 

You may use LOOKUPVALUE Function to add a calculated column.

Column =
LOOKUPVALUE (
    Table2[exe_date],
    Table2[contract], REPLACE ( Table1[TND], 1, 3, "CON" )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors