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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rashidanwar
Advocate II
Advocate II

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

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


1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

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

 

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

6 REPLIES 6
rashidanwar
Advocate II
Advocate II

@dufoq3 , thank you so much 🙂

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

You're welcome 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dufoq3
Super User
Super User

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

 

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

@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!

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

dufoq3_0-1711439046522.png

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.