text.contains to use as lookup in another table in m power query akin to index match in excel

[Schnohren]

Hi all,

An issue I have been struggling with for some weeks now (more like driving me insane tbh) is adding a column to my main data table showing what group an item belongs to. The group column should be based on the item name in the main table and a lookup table stored elsewhere.

It is quite important that it is done in M so it is done in the data modelling.

So basically I want the below output:

based on this lookup table:

I was thinking the solution would be something like this going into the column ***Desired solution***:

if Text.Contains([Item], [Lookup_value]) = True then [Lookup_return] else null

However, I believe that I hit a snag because I am evaluating a text against a list, but I am not sure.

Any and all help is appreciated

Best regards,

Christian

[Schnohren]

Hi @v-rzhou-msft, 

Wonderful job. Thanks for the help. 

Happy holidays. 🙂

BR Christian 

[Schnohren]

Hi @amitchandak ,

Thanks for your response. I don't think it quite qualifies, because in the example you referred we have a one-to-many relation where we have a key (the CustomerName).

Here I want to check the Item column to see if it conatins any of the strings you can find in the Lookup_value column from the other table. Ideally, i would like to return the corresponding Lookup_return value.

\Christian

[v-rzhou-msft · Accepted solution]

Hi @Schnohren 

I think you can achieve your goal by merge and transform your table.

I build two sample tables like yours. I add a custom column in both tables for merge.

Merge = 1

Table1:

Lookup Table:

Choose Table1 to merge(lefter outer) two tables by merge columns.

Add a conditional column in New Table1.

Then we need to duplicate the New Table and group by Item to count null for each item.

We see item = pears 1 kg count = 10, others = 9, so we can distinguish that pears 1 kg = null others has result.

Merge New Table1 with this count table by Item, expand count column.

Remove "Merge","Lookup.Lookup_value","Lookup.Lookup_return" columns and remove duplicate column by selecting all remaining columns.

sort count column by ascending and sort Desired colution column by ascending.

Remove top 5 rows , count column and we can get the final result.

You can download the pbix file from this link: File.pbix

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

The lookup.Lookup_value never works. This is useless solution

[amitchandak]

@Schnohren , refer if this solution , if that can help

https://community.powerbi.com/t5/Desktop/How-to-lookup-values-in-another-table-in-the-Query-Editor/t...