Forum Discussion
create column from different tables
I have 2 tables, and I want to add the Region column to Table 2 based on the Customer Name match. If there is a customer name match, I'd like the region to match what is in Table 1. If there is not a match (i.e. Customer 'C'), I want the value to be Unknown. Can someone assist me with this?
Table 1
| Customer Name | Region |
| A | USA |
| B | Europe |
Table 2
| Customer Name | Region (desired column) |
| A | USA |
| B | Europe |
| C | Unknown |
hi Anonymous
You could use LOOKUPVALUE Function to create a calculate column as below:
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
Region = var _region= LOOKUPVALUE('Table 1'[Region],'Table 1'[Customer Name],'Table 2'[Customer Name]) return IF(ISBLANK(_region),"Unknown",_region)Result:
Regards,
Lin
2 Replies
Hi Anonymous ,
See the file attached. You just need to handle it on Power Query (clean values, merge with left or right (depends on where you start it, and finally replace null to Unknown like you wish).
Ricardo
hi Anonymous
You could use LOOKUPVALUE Function to create a calculate column as below:
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
Region = var _region= LOOKUPVALUE('Table 1'[Region],'Table 1'[Customer Name],'Table 2'[Customer Name]) return IF(ISBLANK(_region),"Unknown",_region)Result:
Regards,
Lin
