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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

change value of a column based on another table column

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?

2 ACCEPTED SOLUTIONS

Try this

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

View solution in original post

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

View solution in original post

10 REPLIES 10
Vijay_A_Verma
Super User
Super User

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
Not applicable

it does not work, the condition always fail

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
Not applicable

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

this is my t2:

andlor_0-1660220358189.png

and this is my t1:

andlor_1-1660220440287.png

the formula i used is:

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

 

and it gaves me always null

 

Try this

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

still null

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors