Non equi join in power bi

[maddukuri451]

Hi , 

we have below requirment to join based on prase and pattren like below.

 Table 1:  

Jobnames

L_secret_keys_exp

L_user_keys_ings

L_mars_loads_exp

L_audit_log_exp

Table 2:

col1

audit

secret

user

mars

O/p should be :

col1    | col2

audit | L_audit_log_exp

secret|L_secret_keys_exp

user|L_user_keys_ings

mars|L_mars_loads_exp

[v-piga-msft]

Hi @maddukuri451,

You could follow the steps below to get your output.

1. Edit the two tables in Query Editor and duplicate the jobname column, then Split Column.

2.Close and Apply.

3. Create a calculated column in Table 2 with the formula below.

Column = LOOKUPVALUE(Table1[jobname],Table1[jobname - Copy.2],'Table2'[col])

Then you will get the table you expected.

Hope this can help you!

Best Regards,

Cherry

[maddukuri451]

Thank you very much for your reply , but my job name formate is changing like below, i dont have uniform formates for JobName

col1		col2			col1	|	col2
IDW_hdp_user_keys_exp		emp			emp	|	hdp_emp_keys_exp
DW_hdp_vendor_keys_exp		mar			mar	|	hdp_mars_keys_exp
hdp_customer_keys_exp		june			june	|	hdp_june_keys_exp
hdp_emp_keys_exp		user			user	|	hdp_user_keys_exp
YYY_hdp_01_mars_keys_exp		customer			customer	|	hdp_customer_keys_exp
hdp_june_keys_exp		vendor			vendor	|	hdp_vendor_keys_exp

Hope you got my issue, waiting for reply .. Thanks in Advance.

[maddukuri451]

Hello Team,

Need help to get below result.

Table 1		Table2			query o/p should be
col1		col1			col1	|	col2
hdp_user_keys_exp		emp			emp	|	hdp_emp_keys_exp
hdp_vendor_keys_exp		mar			mar	|	hdp_mars_keys_exp
hdp_customer_keys_exp		june			june	|	hdp_june_keys_exp
hdp_emp_keys_exp		user			user	|	hdp_user_keys_exp
hdp_mars_keys_exp		customer			customer	|	hdp_customer_keys_exp
hdp_june_keys_exp		vendor			vendor	|	hdp_vendor_keys_exp