Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

like operator as join condition between tables

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)

Need Help

16 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    5 years ago
    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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 

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      thank you!! i will test it

      in the meanwhile, can you please explain the formula?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • camargos88's avatar
        camargos88
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Anonymous ,

         

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous,

    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