Text.Contains if Text contains keyword as subtext

user_13562186 0
Viewed 80

I wish to split the sexes of animals from sentences shown in the desired column using Text.Contains:

enter image description here

In this slightly unusual case male is contained within female and so all results return male.

How can I Modify this code to achieve this?

Current M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], "male", Comparer.OrdinalIgnoreCase) then "Male" else null)
in
    #"Added Custom"

update:

This is my current solution:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Females", each if Text.Contains([Column1], "Female", Comparer.OrdinalIgnoreCase) then "Female" else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Males", each if Text.Contains([Column1], "Male", Comparer.OrdinalIgnoreCase) then "Male" else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Sex", each if [Females] = "Female" then "Female" else if [Males] = "Male" then "Male" else null),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Column1", "Sex"})
in
    #"Removed Other Columns"

However, I don't really like the need for multiple Custom columns and feel there will be a more elegant solution.

powerquery excel contains split
1 Answers

I'd suggest to split the input into a list-object and check if the list contains either "female" or "male" (or neither):

if List.Contains(Text.Split([column1]," "), "female") then "female" else if List.Contains(Text.Split([current]," "), "male") then "male" else null

Or, in M-Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.Contains(Text.Split([column1]," "), "female") then "female" else if List.Contains(Text.Split([column1]," "), "male") then "male" else null)
in
    #"Added Custom"

enter image description here

Maybe, if you want to run the risk of retrieving false positives, try List.FindText:

List.First(List.FindText(Text.Split([column1]," "),"male"))

Or in M-Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"current", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.First(List.FindText(Text.Split([column1]," "),"male")))
in
    #"Added Custom"
user_97581940
Related