Create a relationship between tables similar to SQL LIKE clause

[Anonymous]

Hi dear community experts, 

hope my message finds you well.

The issue I am facing now is the following:

I have 2 tables and each table contains promotion code column. I am planning to set up a relationship between those 2 tables based on that column, which is the only option. 

My problem is the values from code column (Table1) and code column(Table 2) are not identical. In Table 2 some additionl letters are generated and added to inital base code. 

For example the value family20 from Table1 refers to the following list of values in Table 2:

1. family20abc
2. family20def
3. family20tfr
4. family20poi
5. family20bhn

In SQL this could be done using LIKE operator in WHERE clause. However, the modelling is done in Power BI, so alternative solution needs to be applies. 

I have checked NATURALLEFTOUTERJOIN Dax function, however I do not see as it can work. 

Will be very grateful for any advice/tip you can share on this. 

Many thanks in advance and nice weekend to all. 

Cheers, 

Saro

 

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case, you could try this simple way as below:

Step1:

Create a new code column in table 2 as below:

New code = FIRSTNONBLANK(FILTER(VALUES('Table 1'[code]),SEARCH('Table 1'[code],'Table 2'[code],1,0)),1)

Step2:

Then use this new code to create a relationship with table1.

here is simple sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Dear

@v-lili6-msft  thanks for magic. Really appreciate it!

Sincerely, 

Saro

[Anonymous]

@v-lili6-msft 

Nice Idea 🙂

It reminds me of how I used play with strings in excel using LEFT, RIGHT, MID, SEARCH, etc...  Usually these kind of ideas comes to people who are really good in excel formulas and then learn DAX than people who learns DAX first. 

I liked it. 🙂

[parry2k]

@Anonymous why not in Power Query create another column and drop the extra letters from the code column so that you can create relationship between these two tables.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k 

thanks for reply. There is not strict rule or delimeter I can use for that purpose. 

The initial/base codes from Table 1 have different lenght, so respectively different are the lenghts od codes from Tables 2 where additional piece is added. 

Any recommendation with this limitation being relevant?

Thanks again!

[parry2k]

@Anonymous maybe use fuzzy match in power query and get code from another table, although if you can solve it at the source, it will be the best solution.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Thanks @parry2k 

[Anonymous]

Dear  @Fowmy , @amitchandak 

will be very grateful, if you could share your opinion on this. 

Thanks in advance.