Hello,
I'm trying to search for specific text (list) in proximity to any value from a related column within each instance of a description column.
I am working with species mortality data and am trying to extract whether a species name is found near keywords {"dead", "mortality", etc} within a large description column.
I am currently using @Imke Feldmann's solution to search for species names within the Detailed info column:
List.Select(Species[Species Name], (x) => Text.Contains([Detailed info], x, Comparer.OrdinalIgnoreCase)
And have been using a custom fx to extract multiple keywords from the Detailed info column:
let
Text.ContainsAny = (string as text, list as list) as logical =>
let
output = List.AnyTrue(List.Transform(list, (substring) => Text.Contains(string, substring)))
in
output,
Text.ContainsAny([Detailed info], {"dead", "mort", "carcass"})
However, without some kind of proximity modifier, the mortality keywords can get assigned to the wrong species in the case of predation, etc. Essentially I would like to add a column that says "each if any species name is found in proximity to
a mortality keyword within the Detailed info column, then "Mortality", else null". I am still fairly new at M Query and have not been able to solve this one!! Any help would be greatly appreciated!
