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

      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.