How to apply if statement on each element of a lists in a column.

[rashidanwar]

Hi Everyone

I have a country column in a table containg list of values. Now I want to iteate each list of the column and within that list each element of the of the list and want to apply if condition.

Here is the Example.
country
List (List contains 3 values. SA  US  UK)
List (List contains 2 values. AE  NL)
List (List contains 1 values. GE)

I am using List.Tranform() function to itereate on each element in combination with if and else if statement.
Like 
List.Tranform(
                     [country], each
                     if _ = "US" then "America"
                     else if _ = "SA" then "Saudi Arabia" 
                     else if _ = "UK" then "United Kingdom"
                     else _
)

But the problem is, it is applying the if condition only first element of each list and not to the entire list.

Anly help would be highly appreciated.

Regards
Rashid Anwar

[rashidanwar]

@dufoq3 , thank you so much 🙂

[dufoq3]

You can even use coutry list online from web (just use anonymous credentials):

let
    Source = #table(type table[Country=text], {{{"UK", "NL", "US"}}, {{"CZ", "SK"}}, {{"DE"}}}),
    WebCountries = [ Source = Web.BrowserContents("https://www.iban.com/country-codes"),
    Html = Html.Table(Source, {{"Column1", "TABLE[id='myTable'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='myTable'] > * > TR > :nth-child(2)"}, {"Column3", "TABLE[id='myTable'] > * > TR > :nth-child(3)"}, {"Column4", "TABLE[id='myTable'] > * > TR > :nth-child(4)"}}, [RowSelector="TABLE[id='myTable'] > * > TR"]),
    PromotedHeaders = Table.PromoteHeaders(Html, [PromoteAllScalars=true]),
    ToCols = Table.ToColumns(Table.SelectColumns(PromotedHeaders,{"Country", "Alpha-2 code"})),
    CountryNames = Function.Invoke(Record.FromList, ToCols)
  ][CountryNames],
    StepBack = Source,
    Ad_CountryName = Table.AddColumn(StepBack, "Country Name", each List.Transform([Country], (x)=> Record.FieldOrDefault(WebCountries, x, x)) , type list)
in
    Ad_CountryName

[dufoq3]

You're welcome 😉

[dufoq3 · Accepted solution]

Hi @rashidanwar, it works:

let
    Source = #table(type table[Country=text], {{{"UK", "NL", "US"}}, {{"CZ", "SK"}}, {{"DE"}}}),
    AddedCustom = Table.AddColumn(Source, "Custom", each List.Transform([Country], each if _ = "UK" then "United Kingdom" else if _ = "NL" then "Netherlands" else if _ = "CZ" then "Czech Republic" else "Other Country"), type list)
in
    AddedCustom

but I recommend creating helper (converter) table instead of nested ifs:

let
    Source = #table(type table[Country=text], {{{"UK", "NL", "US"}}, {{"CZ", "SK"}}, {{"DE"}}}),
    HelperTable = Table.Buffer(#table(type table[Shortcut=text, Country=text], {{"US", "United States"}, {"NL", "Netherlands"}, {"CZ", "Czech Republic"}, {"SK", "Slovakia"}, {"UK", "United Kingdom"}, {"DE", "Germany"}})),
    StepBack = Source,
    Ad_CountryName = Table.AddColumn(StepBack, "Country Name", each List.Transform([Country], (x)=> try HelperTable{[Shortcut = x]}[Country] otherwise x), type list)
in
    Ad_CountryName

[rashidanwar]

@dufoq3 , thank you very much for your time and effort. I tried but for me it's not working. I think I missed one step. Inititially I have country codes as a comma separated text values and then I convert them into list column. Code is as follows

let
Source = #table(type table[employee=text],
{
  {"UK, NL, US"}, {"CZ, SK"}, {"DE"}
}),
Custom1 = Table.AddColumn(Source, "country", each Text.Split([employee], ",")),
Custom2 = Table.AddColumn(Custom1, "output", each List.Transform([country],
   each if _ = "UK" then "United Kingdom"
   else if _ = "NL" then "Netherlands"
   else if _ = "US" then "United States"
   else if _ = "CZ" then "Czech Republic"
   else if _ = "SK" then "Slovakia"
   else if _ = "DE" then "Germany"
   else if _ = "CZ" then "Czech Republic"
   else "Other Country")
)

in
Custom2

Could you please copy paste the above code in Power Query and see the output? Thanks!

[dufoq3]

The problem is that you've splitted only by comma, but you should consider comma and space. Edit Custom1 step like this:

or this:

= Table.AddColumn(Source, "country", each List.Transform(Text.Split([employee], ","), Text.Trim))

But like I mentioned before - it is better to create separate bridge table - see my second solution in prev. post.