Lookup value based on 2 conditions, one of which is fuzzy match

[Anonymous]

Hello,

I have 2 tables:

Table 1 contains cost stations codes and cost stations names in SAP. The cost stations can be repeated if there are many sections underneath this cost station:

Cost station codes	Cost station names
5271	Factory 1 - Vegetable processing section
5271	Factory 1 - Meat processing section
5831	Factory 3 - Meat packaging

Table 2 contains cost station codes, cost stations names and index in a different in-house system. The cost station codes are the same with Table 1 but the cost station names are diffrent. The cost station names are also not following any rules. Index is unique.

Cost station codes	Cost station names	Index
5271	Vegetable processing unit/Factory 1	1
5271	Meat processing unit - factory 1	2
5831	Factory 3 - Meat package	3

I want to have another column of Index added to Table 1.

Edit: I want to get the Index lookup that takes into consideration Cost Station Names because the Cost Station Codes are not unique for both tables. If Cost Station Names from Table 1 is similar (fuzzy match) to Cost Station Names from Table 2, check if the cost station codes matches. If they do then get index of the match, else look up only Cost Station Code.

How can I achieve this? 

Thank you in advance 🙂 

[VahidDM]

Hi @Anonymous 

Do you want an Index Column in the table with the same value as per Table 2 based on the "Cost station"?

If yes, use LOOKUPVALUE as below to add a new column with DAX:

New Index = lookupvalue(Table2[Index], Table2[Cost station],Table1[Cost station])

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

Appreciate your Kudos🙏!!

[Anonymous]

Hi VahidDM,

Thank you for your quick answer. The Cost Station is not unique value from both tables so I want to look up values that takes into consideration Cost Station Names. For example, if Cost Station Names from Table 1 is similar to Cost Station Names from Table 2, then check if the Cost Station Code matches. If it matches, then get Index.

[VahidDM]

@Anonymous 

Try to use CONCATENATE code to join "Cost Station Names" and "Cost Station Code" into one text string in both tables (Add a new column to each table), then use the new columns in LOOKUPVALUE:

New Index = lookupvalue(Table2[Index], Table2[CONCATENATE],Table1[CONCATENATE])

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

Appreciate your Kudos✌️!!

[Anonymous]

Hello,

I have tried and this does not work because Error: A table of multiple values was supplied where a single value was expected. Also, the Station names in Table 1 and Table 2 are different, they are slightly similar, not 100% similar , therefore I wonder can Lookupvalue does the job?