PowerQuery, Use partial keywords auto-match

[nikio8]

Hi all, thanks for your help in advance.
Essentially reconciling very similar description data every day.

Sometimes there are the same description which can give meaning to several columns (has tax, which company, which accounting expense code...) and very little needs to be done.

The problem is other data either has date or $ amounts, which vary. It would be great to partially search and auto-match. Perhaps unique keywords can be set up, and merge table can be used. It would be nice to set up database (table) that does say ,

AMEX $23.58 8989889 | "AMEX8989889" | => (store name, credit card, Amex, exp code)
CompanyNameX 22/7/2018 | "CompanyNameX" | ...
ContractorA | "ContractorA" | ...

The point of this would be to have table that is easy to edit. It is almost the same data every day, why use complex formulas and many "if else" ?

This is were I am thus far. If rows in Data1 have any of the conversion words, they are stored in output table.
I would like to returrn first word from Converion or null.

Data1		Output		Conversion
The red barn		The red barn		Red
A yellow car		Blue jeans		Blue
Blue jeans		Green beans		Green
Green beans		Chicago blue
Atlanta
Chicago blue

<tbody>
</tbody>

let    Source = Excel.CurrentWorkbook(){[Name="DataTable"]}[Content],    
    LookUp = Excel.CurrentWorkbook(){[Name="ConversionTable"]}[Content],
    #"Value" = 
        //Table.SelectRows(table as table, condition as function) as table         
        Table.SelectRows(Source, (r)=>   List.AnyTrue(
                                                    List.Transform(
                                                        LookUp[Conversion],
                                                        each Text.Contains(r[Data1], _, Comparer.OrdinalIgnoreCase)
                                                        ))
                                                    
                        )
in
    #"Value"

 

[VBA Geek]

List.Select takes the list named ListToSearch and filters it based on the result of the logical expression that was used as second argument (Text.Contains), if true it keeps the item if false it discards it

once ListToSearch has been filtered, Text.Combine takes this list and combines it using a comma as separator

List.Select(ListToSearch, Text.Contains) = keep only the items that are part of the current description
Text.Combine combine the filtered items by a comma