Merge queries different values

[UlisesCiccola]

Hello everyone!

I have the following problem and would like to know the best way to face it. I have a typical sales table that has the following columns order id, sales amount, neighborhood name. This neighborhood name is a text field that has 60 distinct values written in a specific way.

I have an external table with data from this neighborhoods and would like to merge this or join it with the sales table, but of course this external table with the column "neighborhood names" has 60 distinct values with a different naming convention.

How should I address this problem to merge the two tables or be able to join by the neighborhood column?

Thank you!!!

[mahoneypat]

Please show examples of the two neighborhood formats, and maybe a conversation formula can be suggested for an added custom column, so you can make a direct relationship.

Regards,

Pat

[UlisesCiccola]

@mahoneypat unfortunately there is no pattern, I think @Jimmy801 answer is more accurate. A lookup table is more aproppriate

[Jimmy801 · Accepted solution]

Hello @UlisesCiccola 

why not create a lookup table, where you put the version of the sales table in one column and on the other the version of your neighborhood-table? As you have only 60 values, this should be no issue. Load all three tables into your datamodel and connect them or use you connection-table to add a new column to one of your other tables that then allows you to connect them in your data model.

You could also try a Table.FuzzyNestedJoin where you can join tables on columns that are not written exactly the same. You can implement some options and a treshold as well. But there you could face the risk that no or more rows are found. So try to go for the first option. You can create however a manual table in Power BI by clicking "Enter Data" in the menue.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[UlisesCiccola]

Thank you @Jimmy801 !! I will go with the first option! I do not know how to make this lookup table but I will have a look. It sounds like the right way to go