like operator as join condition between tables

[liorro]

hi,

i want to join (inner) between table A to B based on LIKE opeartor

logic: ON A.Column LIKE '%' + B.Column+ '%'

example:

XXXXXX8074396XXXXXXX LIKE '%' + 8074396+ '%' ----> there is a much

XXXXXX1074333XXXXXXX LIKE '%' + 8074396+ '%' ----> there is not amuch

i tried to use fuzzy much but it ddint worked (it return table with 2 line and not 1)

is there a solution for it? (its a lrage data to do such in SQL)

[Anonymous]

HI @liorro,

Did these replies help for your scenario? If this is a case, you can kudo or accept it if that one helps/solve your scenario to help others who faced a similar issue find it more quickly.

Regards,
Xiaoxin Sheng

[camargos88]

Hi @liorro ,

Try creating a column like this:

let _phrase = [Column1] in

List.Select(Item[Item], 
each Text.Contains(_phrase, Number.ToText(_))
)

 

Also, see the attached file for more info.

[liorro]

it worked but not performing well on my data (~3K rows)
do you have any other idea?

[camargos88]

@liorro ,

Can you share your pbix ? Masking any sensitive info if necessary.

[liorro]

the data is sensitive and therefore i cant share it with you

[camargos88]

@liorro ,

How long is it taking to process this new column ?

[liorro]

table a ~9K rows

table b ~8K rows

its took more than 3 hours 

[Anonymous]

Hi @liorro,

In fact, if you execute complex looping formulas on large amount of records, they will be processed with poor performance and spend more time. You can try to package them with buffer functions to prevent the memory leak on looping calculate if it helps.

How and When to use List & Table Buffer? 

In addition, you can also consider using Dax expressions to handle this scenario. Add a calculated field in your tables to extract the core character strings you searched, then you can create a calculated table to merge these fields and use it as the bridge to link these tables.

From SQL to DAX: String Comparison 

Regards,

Xiaoxin Sheng

[camargos88]

@liorro ,

How long is the refresh without the new column ?

[liorro]

~15 min

[camargos88]

@liorro ,

I've tested by my side with csv files over 10k rows. It took less than 2minutes to process.

What is your source ?

Also can you show the code you used to create this new column ?

[liorro]

my source is view in SQL

i used the code you shared with me

let _phrase = [ValidationFunctionName] in

List.Select(Fact_CalculatedField_SubQuery[FieldTemplateId],

each Text.Contains(_phrase, Number.ToText(_))

)

[camargos88]

@liorro ,

Once you are using SQL, I believe you should try pushing this process to the source.

Is it an option to write this join inside the view ? Or even use it to do this join ?

It should be faster than bringing the values to the Power Query and process every row.

[liorro]

thank you!! i will test it

in the meanwhile, can you please explain the formula?

[vanessafvg]

where are you trying to do this join exactly? Are you using power query / dax?

please provide more information about what you are trying to do.

[liorro]

thanks! Table A and B are views in SQL im using as a source in Power BI

i don't familiar with DAX which can help me

i tried to merage between the table using fuzzy match via power query