Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.