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]

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.

Instead of creating the "Output" table you want to add a column to the "Data1" table which shows, for each row, the first (partial) match found in the Conversion list ???

 

[nikio8]

Hi yes. The first row in conversion table is the keyword i would use to add more columns to description. Convert Description to book values.

The Red Car --- Red --- Red, MotorVehicle, Input tax credits, State, Registration, Accounting code...

Of course conversion table would not have ambiguous names.

Each string in Description, search all keywords in Conversion and return first non empty keyword or null.

 

[nikio8]

This is what I am after but in power query.

https://youtu.be/_bdHe3z_fVU

 

[VBA Geek]

Does this do what you are after?

let    
    Source = Excel.CurrentWorkbook(){[Name="DataTable"]}[Content],

    LookUp = List.Buffer(Excel.CurrentWorkbook(){[Name="ConversionTable"]}[Content][Conversion]),

    fn = (Description as text, ListToSearch as list) =>
    Text.Combine(List.RemoveNulls(List.Transform(ListToSearch, each if 
                                                                      Text.Contains(Description, _, Comparer.FromCulture(Culture.Current, true)) 
                                                                    then 
                                                                        _ 
                                                                    else 
                                                                        null
                                                )
                                ),
                 ","),

    AddColumn = Table.AddColumn(Source,"Result", each fn([Data1], LookUp), type text)
in
    AddColumn

 

[VBA Geek]

Actually I could've used List.Select instead of List.Transform to make it more compact and avoid using List.RemoveNulls. You can try doing that since I see you have M knowledge

 

[nikio8]

Very nice
Thank you. Thank you VBA Geek.
I see that I have a lot to learn.

Very nice example how to convert table column to list and how to use .Transform, quite tricky function.

 

[nikio8]

Hi, not sure what is the problem, there is an error "Expression.Error: We cannot convert the value "Red" to type Logical."
I assumed that I can just replace List.RemoveNulls(List.Transform )) with List.Select as per your instruction.
I am not exaclty sure what Comparer.FromCulture(Culture.Current, true) does ?

let        LookUp = List.Buffer(Excel.CurrentWorkbook(){[Name="CTable"]}[Content][Original]),    
    Source = Excel.CurrentWorkbook(){[Name="DTable"]}[Content],    


        fn = (Description as text, ListToSearch as list) =>
            Text.Combine( 
                            List.Select(
                                            ListToSearch, 
                                            each if 
                Text.Contains(Description, _, Comparer.FromCulture(Culture.Current, true)) then _ else   null
                                                        
                                       ),
                       ","),


    AddColumn = Table.AddColumn(Source,"Result", each fn([Data1], LookUp), type text)
 in
    AddColumn

 

[VBA Geek]

If you use List.Select then you will not need to use an if statement:

each if 
                Text.Contains(Description, _, Comparer.FromCulture(Culture.Current, true)) then _ else   null

List.Select iterates through the List and keeps only those items that evaluate to TRUE, hence you can simply do:

each Text.Contains(Description, _, Comparer.FromCulture(Culture.Current, true))

 

[nikio8]

uh thanks, quite confusing.
looking at text.combine it would seam that rather than taking one description and searching all keywords, it does the opposite, as comma is outside of .select.

not easy at all