Forum Discussion
Solved
Conditions containing multiple strings in a column
I am trying to make conditions that contain certain strings and all the solutions I found online didnt work or tedious. I have a column that contains over 400 cities and I am trying to group them int...
Okay foyadeyi in that case we have to create a custom column through "Edit Query" with below code:-
if List.AnyTrue(List.Transform({"Berlin","Bremen","Hamburg"},(substring ) => Text.Contains([city],substring))) then "North" else if List.AnyTrue(List.Transform({ "Munich", "Ausgburg", "Wurzburg"},(substring ) => Text.Contains([city],substring))) then "South" else if List.AnyTrue(List.Transform({ "Luverkusen", "Bielefeld", "Trier" },(substring ) => Text.Contains([city],substring))) then "West" else [city]
Thanks Samarth_18 This works, however the code was unable to pick cities that contain numbers or symbols as well. Example. "Berlin 1" or "Bremen-xxx" is not being seen/classified as North. How do i solve this?
Okay foyadeyi in that case we have to create a custom column through "Edit Query" with below code:-
if
List.AnyTrue(List.Transform({"Berlin","Bremen","Hamburg"},(substring ) => Text.Contains([city],substring)))
then
"North"
else if List.AnyTrue(List.Transform({ "Munich", "Ausgburg", "Wurzburg"},(substring ) => Text.Contains([city],substring)))
then "South"
else if List.AnyTrue(List.Transform({ "Luverkusen", "Bielefeld", "Trier" },(substring ) => Text.Contains([city],substring)))
then "West"
else [city]