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.

[TABLE="width: 371"]
<tbody>[TR]
[TD]Data1[/TD]
[TD][/TD]
[TD]Output[/TD]
[TD][/TD]
[TD]Conversion[/TD]
[/TR]
[TR]
[TD]The red barn[/TD]
[TD][/TD]
[TD]The red barn[/TD]
[TD][/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]A yellow car[/TD]
[TD][/TD]
[TD]Blue jeans[/TD]
[TD][/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Blue jeans[/TD]
[TD][/TD]
[TD]Green beans[/TD]
[TD][/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Green beans[/TD]
[TD][/TD]
[TD]Chicago blue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Atlanta[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicago blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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