Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Keith011
Helper III
Helper III

Easy match and return help

Hi there.

 

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

 

This is table1

countryCodecountryName
HKHong Kong
MYMalaysia
SGSingapore
CNChina

 

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 Namefinal_country
HKHong Kong
MalaysiaMalaysia
SGSingapore
CNChina

 

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"

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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"])

View solution in original post

4 REPLIES 4
Keith011
Helper III
Helper III

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

 

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

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
Super User
Super User

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"])

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors