DAX Compare a Column imported from power query with another table's Column and return the result from the same row

0
votes

I have a daily CSV file which has a column containing a big list of addresses (about 5000/day) received from a call center, some of those addresses contain the name of the neighborhoods around the city and it's usually in the first 5 words. in another table i have the exact name of all the neighborhoods in one column and in another column in the same row i have the courier name which provides delivery service in that neighborhood.

I'm looking for a method in power pivot to search each row of the [Address] column & if a value similar to the neighborhood's name found give me the name of the courier of that neighborhood.

This an example for my data sets, the fist sheet describes 5 sample of my daily data, and the second sheet is districts information and their courier ID

1
excelpowerbipowerpivotdaxpowerquery
Can you provide any sample data and example(s) of what your desired outcome is/are? How to create a Minimal, Complete, and Verifiable example – Joe Gravelyn
Hello, i edited my request with a link to my sample data – Aslan Spring

1 Answers

0
votes

Disclaimer - this method doesn't handle for similar words, only exact matches.

The easiest method I found is to make a calculated table. On the Modeling tab, click on New Table and enter this formula.

Results = SELECTCOLUMNS(
    FILTER(CROSSJOIN(Addresses, Couriers), SEARCH(Couriers[District], Addresses[Address], , -1) > 0),
    "Address", Addresses[Address],
    "CourierID", Couriers[Courier ID]
)

This cross joins your two tables and then filters the results down to only where the District from the Couriers table can be found in the Address. As I said in the disclaimer, this is only looking for exact matches.

Results table

Then to get that back into the Address table, add a new column with this formula. This will look up the address into the table we just made and return the Courier ID.

CourierID = LOOKUPVALUE(
    Results[CourierID],
    Results[Address],
    Addresses[Address]
)

Results

The reason we need to do this in two steps is because the LOOKUPVALUE function cannot take expressions.