Reputation: 1
I have two tables which have two columns some with similar data but I want lookup values from one of the table when the data in the two columns match and if it does not match it returns data in the master column
TABLE A
Company_Code | Invoice_No | Buyer_Code| Diaspora_Buyer_Code
A|1|001
A|6|002
B|2|003
C|3|001
D|5|006
TABLE B
Company_Code | Invoice_No|Diaspora_Buyer_Code
A|1|11
A|6|12
B|2|11
EXPECTED RESULT
Table A should have the below values in the Diaspora_Buyer_Code(Calculated Column)
TABLE A
Company_Code | Invoice_No | Buyer_Code| Diaspora_Buyer_Code
A|1|001|11
A|6|002|12
B|2|003|11
C|3|001|001
D|5|006|006
Upvotes: 0
Views: 3270
Reputation: 149
Try this
Diaspora_Buyer_Code =
IF(ISBLANK(CALCULATE(FIRSTNONBLANK('Table B'[Diaspora_Buyer_Code],'Table B'[Diaspora_Buyer_Code]),
FILTER('Table B','Table A'[ Invoice_No ]='Table B'[ Invoice_No]),
FILTER('Table B', 'Table B'[Company_Code ]='Table A'[Company_Code ]))),
VALUE('Table A'[ Buyer_Code]),
CALCULATE(FIRSTNONBLANK('Table B'[Diaspora_Buyer_Code],'Table B'[Diaspora_Buyer_Code]),
FILTER('Table B','Table A'[ Invoice_No ]='Table B'[ Invoice_No]),
FILTER('Table B', 'Table B'[Company_Code ]='Table A'[Company_Code ])))
Upvotes: 1
Reputation: 858
Add calculated columns in both tables:
Table A
KeyColumn = 'Table A'[Company_Code]&"-"& 'Table A'[Invoice_No]
Table B
KeyColumn = 'Table B'[Company_Code]&"-"& 'Table B'[Invoice_No]
Method 1
Create a relationship between both key columns. Now the formula for expected calculated column goes as follows:
Diaspora_Buyer_Code =
VAR RelatedVal =
RELATED('Table B'[Diaspora_Buyer_Code])
RETURN
IF (
ISBLANK(RelatedVal),
'Table A'[Buyer_Code],
RelatedVal
)
If you do not want to create the relation then use the following formula for the column:
Diaspora_Buyer_Code =
VAR Lookup =
LOOKUPVALUE(
'Table B'[Diaspora_Buyer_Code],
'Table B'[KeyColumn],
'Table A'[KeyColumn]
)
RETURN
IF (
ISBLANK(Lookup),
'Table A'[Buyer_Code],
Lookup
)
Output in both cases:
Upvotes: 0