cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors