Using wildcard in LOOKUPVALUE

[labrecquev]

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
[TABLE="width: 250, align: left"]
<tbody>[TR]
[TD]Lookup column[/TD]
[TD]Calculated column[/TD]
[/TR]
[TR]
[TD]Ok w.m white[/TD]
[TD]White Marble[/TD]
[/TR]
[TR]
[TD]whitemarble B[/TD]
[TD]White Marble[/TD]
[/TR]
</tbody>[/TABLE]






Lookup table
[TABLE="width: 250"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Return[/TD]
[/TR]
[TR]
[TD]w.m[/TD]
[TD]White Marble[/TD]
[/TR]
[TR]
[TD]whitemarble[/TD]
[TD]White Marble[/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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]

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]

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

 

[ImkeF]

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?