Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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?

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Try this

    = try Foglio1[#"Code Country"]{List.PositionOf(Foglio1[#"Name Country"],[#"Country/Region"])} otherwise null
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

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

10 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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"