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]
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]