Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Multiple IF Statements (50+)

Hello everyone, i have a cloumn full of abbrevations, i need to create a new column with full name   (eg. COUNTRY NAME = IF(Table1[Noc] = "FRA", "France",IF(Table1[Noc] = "GER", "Germany",.... T...
Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    Put your data in a table and use a merge to match the short codes.  You can return the country full name after that.

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    For that many, you almost certainly want to use a table as HotChilli recommends.

     

    I just wanted to mention the SWITCH function which is much cleaner than IF in these sorts of situations:

    https://dax.guide/switch/

     

    Country Name =
SWITCH (
    Table1[Noc],
    "FRA", "France",
    "GER", "Germany",
    "ITA", "Italy",
    "USA", "United States of America"
)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

you could use a record as dictionary from short name to extenden name

 

let
    dict=[fra="france",ger="germany",ita="italy",usa="united states of america"],
    
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSitKVNJRSspJVIrViVZKTy1C4mWWIMuVFsN5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna1", type text}, {"Colonna2", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "extended", each Record.Field(dict,[Colonna1]))
in
    #"Aggiunta colonna personalizzata"