Forum Discussion

Digger's avatar
Digger
Post Patron
4 years ago

Variable in columns

I have 1000 countries, 

 

CountryName = "ES",
Position = List.PositionOf(List.Transform(Translate[CountryName], (x) => Text.Contains("tipo", x)), true)

 

 

How to put CountryName  as variable into Table reference? so  Translate[CountryName] should get info from ES column name Translate[ES]

 

now i get error

 

Expression.Error: The column 'CountryName' of the table wasn't found.
Details:
CountryName

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Hi Digger ,

     

    To set something as a variable in Power Quer use let so that would be something like

    let
  x = [Column]
in
  x //or any further transformation)

     

    Additional steps after x and before in will require that that step or variable ends with a comma so that would be

    let
  x = [Column],
  y = Text.Lower(x) //just a sample                                             
in
  y //or any other transformation

     

    • Digger's avatar
      Digger
      Post Patron
      4 years ago

      so you offer to write 1000 if statments instead of just put variable to name? i need put variable to square brackets, but not write 1000s  ifs statments, as it will be not dinamic if new countrie appears