Forum Discussion
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 into regions.
For example if the City column contains Berlin, Bremen or Hamburg then Region should be “North”, if city contains Munich, Wurzburg or Ausgburg then Region should be “South”, Trier, Luverkusen and the likes should be West. I tried putting all the city names in the same string value but it doesnt work. In the end I want something like::
Region = If [City] contains “Berlin, Bremen, Hamburg” then “North”
else if [City] contains “Munich, Ausgburg, Wurzburg” then “South”
else if [City] contains "Luverkusen, Bielefeld, Trier" then "West"
The only solution I know is to write individual Ifs and else ifs for each city but this is a long and tedious task given the number of cities i have to write manually. I know there might be some tricks to do this collectively but i havent been able to find any solution yet
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]
4 Replies
Hi foyadeyi
For your case if else is only option left so instead of multiple individual if and else you can write in this way:-
Region = IF ( City_data[city] IN { "Berlin", "Bremen", "Hamburg" }, "North", IF ( City_data[city] IN { "Munich", "Ausgburg", "Wurzburg" }, "South", IF ( City_data[city] IN { "Luverkusen", "Bielefeld", "Trier" }, "West", City_data[city] ) ) )Thanks,
Samarth
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]
Samarth_18 Thanks a lot. It worked!