Contains Help

[Anonymous]

Im new to PowerBI and i cant seem to get past this hurdle. I have looked through some message board posts but havent found a decent solution to my question. Hopefully you all can help!

I want to add a column to my excel query that references a table of client names and looks in the queried table for matches, but then DISPLAYS the name from the other table.

Example:

Table 1 = Queried Table

A1= Customer Name

A2 = 12345 Chick Fil-A

A3 = 5678 McDonalds

A4 = Pizza Hut New York

Table 2 = Fixed Table

A1 = Customer Name

A2 = McDonalds

A3 = Chick Fil-A

A4 = Pizza Hut

Goal is for Column B in Table 1 to display the corresponding match from Column A in Table 2 (cell B2 would = "Chick Fil-A" and B3 would = "McDonalds". Ive seen alot of posts regarding Contains and LookupValue but cant seem to get either to produce the result im looking for.

Also of note, the text in Table 1 is inconsitent and cannot use any type of delimeter or extract to pull the correct data.

Thanks for your help!

Justin

[Greg_Deckler]

@Anonymous - Here is a DAX way of doing it:

Column = 
VAR __customer = [Customer Name]
VAR __table = GENERATE('Table10',SELECTCOLUMNS('Table11',"Lookup Name",[Customer Name]))
VAR __table1 = ADDCOLUMNS(__table,"Lookup",IF(CONTAINSSTRING([Customer Name],[Lookup Name]),[Lookup Name],BLANK()))
RETURN
MAXX(FILTER(__table1,[Customer Name] = __customer && [Lookup]<>BLANK()),[Lookup])

See Table11 and Table12 of the attached file. Table12, Column is your column "B".

[Anonymous]

Greg,

Took me a little while to learn what your solution was doing and how to port it to my own file, but it worked GREAT!

Thank you very much!!!

[Anonymous]

@Greg_Deckler 

One last question.

The next evolution would be to have PoweBI recognize when 2 entries are similiar. For example: Chick Fil-A and Chick-Fil-A are technically 2 different entries, but are both essentially just Chick Fil-A. So i would like to prevent it from creating 2 lines of the same customer. The end result would be to create a pivot/visualization by Customer so that it counts all the orders for a give customer without it have multiple lines if there are punctuation alterations for the different lines if that makes sense...

Again, thank you for the help!!!

[Greg_Deckler]

A thought here, you might try a Merge Query with Fuzzy Matching.