Forum Discussion

Jun_Wang's avatar
Jun_Wang
Icon for Advocate I rankAdvocate I
7 years ago
Solved

any function can do exact match (text) in power query?

I'm quite new to power query. I used to run some M functions in power query to do keywords searching and assign these rows with specific keywords to a category. But it has some issues now. I need exa...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    You may download my solution Excel file from here.  Go to PowerPivot > Manage

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

Could you share a dataset and show the expected result.  Also, would you be OK with a DAX calculated formula as an alternative?

Jun_Wang's avatar
Jun_Wang
Icon for Advocate I rankAdvocate I
7 years ago

Thanks for replying my question. Here is the screeshot. 

 

Now, if I use the above mentioned nested function searching "lock", it not only returns the text containing the word "lock" but also text containing "block". That's why I want some function can do exact match.

 

If DAX helps out, I'll surely embrace it :)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Jun_Wang,

     

    It works for me, please find the below screenshot for your reference.

     

     

     

     

     

     

     

     

    Go to Edit Queries --> Select the column (Text) --> Add Column Tab --> Conditional Column --> and give the details as in the screenshot. It will work.

     

    Please let me know if you still have any questions. Happy to help you.

     

    Regards,

    Pavan Vanguri.

    • Jun_Wang's avatar
      Jun_Wang
      Icon for Advocate I rankAdvocate I
      7 years ago

      Thanks Pavan, but this fix is not very ideal to me. My dataset normally has 10k rows, the "block" is not the only possible word containing string "^lock". It could be blockade, block, blockage, even sherlock........

       

      I reckon exact match would be easier :)

       

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        padlock is not an exact match.  Therefore why should that be a part of your result?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    How do we make the software understand that block should be ignored but padlock should be considered (when there is no space between pad and lock)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      We created a column on a condition saying that, if the string contains "block" then block else "lock".

      Power BI will search for the defined string in the condition and will reteun the expected results.

       

      Hope I have answered your question.

       

      Regards,

      Pavan Vanguri.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        No, you have not.  Suppose there was a word bullock, you would have to create an exception for this as well.  There will be countless such words.

    • Jun_Wang's avatar
      Jun_Wang
      Icon for Advocate I rankAdvocate I
      7 years ago

      Yes yes yes, you are right. 

       

      I pre-define a collection of words to define it is a lock issue, padlock is one of them as well. I didn't show all my script (trying to catch attention on exact match). 

       

      But the issue is, if the text only mention "lock" and I use the word "lock", the result will bring other words containing "^lock"......This makes my life difficult :(

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

        Share some sample data (which i can paste in an Excel file) and a table showing the words that you would like to be treated as lock.