DAX Search and Lookup (Something like a VLookup)

[jives00]

I have a long list of transaction data that contains many different vendor names. Some of the vendors have a unique name for each and every transaction (e.g. FedEx appends a portion of the tracking # to each vendor name for some reason, Jewel includes the store number, Dell uses DMI with a number sometimes, and Dell other times, etc. etc.).

I want to user PowerPivot to "normalize" some of these names so that it becomes easier to pull total spend and other data (e.g. just show me how much was spent with FedEx). The transaction data is source data, so I'm stuck with it as is.

What I want to do is create another table that says what I'm searching for in each vendor name and what it's normalized name should be. Anytime the phrase fedex appears in the vendor name (start, finish, in the middle, could be anywhere in that field) column in the source data, PowerPivot should know that's actually FedEx. I have no idea what this list of vendors to normalize is and it will probably change over time so it needs to be flexible and not have to update the formula each time.

I have no idea what the formula is then to link these together. I'm picturing a calculated field in the transactions table that:

1) Looks at the Transactions[VendorName] column
2) Searches it against the AutoName[SearchTerm] column
3) If it finds a match (they will not be exact matches) add the AutoName[NormalName] in the calculated field.

Table: Transactions
Vendor Name | Amount
FedEx 42351 | $15.24
FedEx 58563 | $12.11
Jewel #1231 | $89.12
DMI | $2003.24
Dell | $1254.52

Table: AutoName
SearchTerm | NormalName
fedex | FedEx
jewel | Jewel
dmi | Dell
dell | Dell

Table: Transactions w/ calculated column
Vendor Name | Normal Name | Amount
FedEx 42351 | FedEx | $15.24
FedEx 58563 | FedEx | $12.11
Jewel #1231 | Jewel | $89.12
DMI | Dell | $2003.24
Dell | Dell | $1254.52

Thoughts? Thanks for the help!

 

[Kazlik]

Will there only be one match per transaction or can there be many per transaction?

 

[jives00]

Per transaction - just 1. So a line item showing we paid FedEx 1234, would only need to show FedEx in the calculated field.

 

[Kazlik]

You might try Power Query to clean up this data first. Split the Vendor Name column on space and then do some replace values. Then as new variations come in just add them to your replace list

 

[scottsen]

See if this helps you?
tinylizard.com/dax-contains-function/

What you are trying to do is certainly possible.

 

[jives00]

The Contains function might work, I just need to figure out the syntax. Thoughts? Basically if [column] contains [lookup column] put in [another lookup column]. E.g. If the Vendor Name contains FedEx (even if it has extra letters/numbers in it somewhere), then make the Normal Name FedEx. I'm not sure what exactly that formula is yet. Also, do you know if Contains can be restricted further in some way... e.g. Dell could show up as a vendor but also things like Michael O'Dell.

The Power Query splitting on space won't work, we have a good deal of vendors who have more than one word in their name.

 

[scottsen]

Ya, I was on crack w/ the contains think

I think I called your AutoName my "Lookup". Go find the first row that isn't blank... by evaluating each Lookup name... and using it as the first param to search -- returning blank if it doesn't match. or something like that

=FIRSTNONBLANK(Lookup[Lookup], search(Lookup[Lookup], Names[Full Name], 1, BLANK()))

 

[jives00]

Thanks close, thanks!

(Scratch my previous comment if you saw it already)

I changed the formula slightly to be: =FIRSTNONBLANK(Lookup[Lookup], search(Lookup[Lookup], [Original Name], 2, BLANK()))

This lets me put in two columns, one for the name it's searching on (e.g. FDEX) and the other what I want (e.g. FedEX). So it works, awesome and thank you!!

However, this raises another issue - sometimes it has a false positive. For example I saw a name "Tina Crandell" got renamed to "Dell". Any thoughts on how we can build in some logic around that? Maybe something like it must be a search on full word, not as part of another word?

Thanks again!

 

[jives00]

Hm, it won't let me edit the post now. Ignore the formula change. Using 2 doesn't do what I thought it did so I went back to 1.

Still have the search issue though (Crandell becomes Dell) and names that aren't in the name (e.g. FDEX needs to become FedEx). Thoughts?

 

[scottsen]

Ick. This sort of data cleaning is better suited to... almost any tool but Power Pivot Likely Power Query would be a least "better" (I dont think it supports regular expressions yet though) --- you could split by whitespace and do some matching maybe.

I typically script something in power shell, but I love it.