change value of a column based on another table column

[Anonymous]

I have 2 tables:

t1) with country_name (col1) and country_code (col2)

t2) with country_name

I want to add a new column in t2 containing country_code of t1 based on the value in country_name  from t2.

How can I do it?

[Vijay_A_Verma · Accepted solution]

Try this

= try Foglio1[#"Code Country"]{List.PositionOf(Foglio1[#"Name Country"],[#"Country/Region"])} otherwise null

[Vijay_A_Verma]

In a custom column in t2, use below formula

= try t1{[country_name=[country_name]]}[country_code] otherwise null

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEtOVYrViVbyzEvJTFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country_name = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try t1{[country_name=[country_name]]}[country_code] otherwise null, type text)
in
    #"Added Custom"

 You can also use merge

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEtOVYrViVbyzEvJTFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country_name = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"country_name"}, t1, {"country_name"}, "t1", JoinKind.LeftOuter),
    #"Expanded t1" = Table.ExpandTableColumn(#"Merged Queries", "t1", {"country_code"}, {"country_code"})
in
    #"Expanded t1"

[Anonymous]

it does not work, the condition always fail

[Vijay_A_Verma]

Download this Excel where it is demonstrated that code is working.

https://1drv.ms/x/s!Akd5y6ruJhvhuhTQFpr4zilBbqsl?e=Dmp1Li 

Let me know what error you are getting.

[Anonymous]

I am not getting error but the new column is populted with null in every row.

this is my t2:

and this is my t1:

the formula i used is:

try Foglio1{[#"Name Country"=[#"Country/Region"]]}[#"Code Country"] otherwise null

and it gaves me always null

[Vijay_A_Verma · Accepted solution]

Try this

= try Foglio1[#"Code Country"]{List.PositionOf(Foglio1[#"Name Country"],[#"Country/Region"])} otherwise null

[Anonymous]

still null

[Anonymous]

I tried to print the index but it is always -1 like the country is never found

[Vijay_A_Verma]

Is it possible for you to share some sample data either in Excel or pbix format both t1 and t2?

[Anonymous]

https://docs.google.com/spreadsheets/d/1fY-RKrjSY20kcgFJu_3VxN4AcbRJkLbGdWHuXGtyx40/edit?usp=sharing...

here is a part of it

[Vijay_A_Verma · Accepted solution]

It gives the result for France. For other countries, they are missing in Froglio1, hence giving null.