Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
You may use LOOKUPVALUE Function to add a calculated column.
Column = LOOKUPVALUE ( Table2[exe_date], Table2[contract], REPLACE ( Table1[TND], 1, 3, "CON" ) )
You may use LOOKUPVALUE Function to add a calculated column.
Column = LOOKUPVALUE ( Table2[exe_date], Table2[contract], REPLACE ( Table1[TND], 1, 3, "CON" ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |