# Using wildcard in LOOKUPVALUE



## labrecquev (Feb 22, 2016)

Hi guys, first post here,

I've been working on a problem for a couple of days in PowerPivot.

I want a new column to lookup for a match between some text contained in a string and a lookup table, and to return column 2 of the lookup table.

Acutal table

*Lookup column**Calculated column*Ok w.m whiteWhite Marblewhitemarble BWhite Marble

<tbody>

</tbody>





Lookup table

*Value**Return*w.mWhite MarblewhitemarbleWhite Marble

<tbody>

</tbody>
I've tried using wildcards in the LOOKUPVALUE different ways:
=LOOKUPVALUE(lookuptable[Return], "*lookuptable[Value]*", actualtable[LookupColumn])

I've also tried "*", or only *, without result...

I was able to make it work in excel with INDEX(MATCH), but not in PowerPivot.


----------



## ImkeF (Feb 22, 2016)

This can be done in Power Query, as you can see in the attached file: https://www.dropbox.com/s/qb3ornzaoqydn4r/ME_UsingWildcardLookupvalue.xlsx?dl=0

It also contains some problems you might run into with black and blue marble for example b.m and b.m ?
So check your results - you might need to adjust your formulas for some special combinations.


----------



## Heureka (Apr 28, 2016)

ImkeF said:


> This can be done in Power Query, as you can see in the attached file: https://www.dropbox.com/s/qb3ornzaoqydn4r/ME_UsingWildcardLookupvalue.xlsx?dl=0



I was looking for this solution for quite some time. I was able to replicate it to my data and it works like a charm! Thank you very much!
BUT..... My Data set is a lot bigger than the example shown: Data table around 80.000 lines and Lookup table with over 125.000 lines. The solution works still in principle, but it takes awefull long to refresh the data - to the point that its better to get back to Excel VLOOKUP wildcard search 
Any suggestion for an optimization with PQ?


----------



## ImkeF (Apr 28, 2016)

Try adding a Table.Buffer.

let
    Source = Excel.CurrentWorkbook(){[Name="LookupTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each *Table.Buffer(*ActualTable)*)*,
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Lookup column", "Calculated column"}, {"Lookup column", "Calculated column"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if Text.Contains([Lookup column],[Value])=true then [Calculated column] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Lookup column", "Calculated column"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> ""))
in
    #"Filtered Rows"

You might buffer other items as well if this doesn't help enough - just shout out.


----------



## Heureka (Apr 30, 2016)

Thanks ImkeF for your reply.
Didn't help yet  It's loading the data with approx 1 row per sec 
More ideas?


----------



## ImkeF (Apr 30, 2016)

This will depend on the structure of your lookup-values: Are they one-word (no spaces) or do they contain combinations with spaces in between?


----------

