Easy match and return help

[Keith011]

Hi there.

I have a simple task but just can't figure it out.

This is table1

countryCode	countryName
HK	Hong Kong
MY	Malaysia
SG	Singapore
CN	China

This is table2

Country/Region Name

HK

Malaysia

SG

CN

I want to add a column (final_country) in table2 that looks something like this

Country/Region Name	final_country
HK	Hong Kong
Malaysia	Malaysia
SG	Singapore
CN	China

so basically, the "final_country" column value will depend on "Country/Region Name". If its like HK, SG and CN it should refer to table1  and give me the countryName else give me the original value like "Malaysia"

[Keith011]

@wdx223_Daniel it's working now! so sorry , some typo of the column names.

1 question : what does the "?" do in your solution?

[wdx223_Daniel]

use ? to get a null when the result of former statement is a error.

use ?? to get the latter statement result if the former gives a null.

[wdx223_Daniel · Accepted solution]

if the rows of table 1 is unique, then try this code:

NewStep=Table.AddColumn(Table2,"Final_Country",each Table1{[CountryCode=[#"Counrty/Region Name"]]}?[CountryName]? ??[#"Counrty/Region Name"])

[Keith011]

Yes table 1 is unique

= Table.AddColumn(#"NoRegisterYesAttend","Final_Country",each #"Country Look Up Table"{[CountryCode=[#"Country/Region Name"]]}?[CountryName]? ??[#"Country/Region Name"])

"NoRegisterYesAttend" is my 2nd table name

"Country Look Up Table" is my 1st table name

but it gives me the error - Expression.Error: A cyclic reference was encountered during evaluation.