I wish to split the sexes of animals from sentences shown in the desired column using Text.Contains:
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.
CodePudding user response:
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"
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"