M query proximity text search RRS feed

  • Question

  • 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:

            Text.ContainsAny = (string as text, list as list) as logical =>
                    output = List.AnyTrue(List.Transform(list, (substring) => Text.Contains(string, substring)))
    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!

    Wednesday, November 20, 2019 7:15 PM


  • Hi

    If you haven't found a solution yet could you please expose a realistic sample of your [Detailed info] column and next to it the expected results?

    Friday, November 22, 2019 4:04 PM