cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jun_Wang Frequent Visitor
Frequent Visitor

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 exact match rather fuzzy match. Can anyone tell me if there is a workaround? 

 

Here is the example

My input text is work order description (the context of these description is mainly about building fabrics). This is a huge dataset normally more than 10k rows.

 

Before I run below script I'll get work order description related to lock issue easily. However, the fuzzy match makes my life difficult. It now returns the work order decription contains string "^lock" to me. For example, it thinks the work description with words "blocked toilet....." is a "lock" problem now just because "block" has "lock" ......

 

Table.AddColumn(Gate, "Door & Door Hardware", each if(List.ContainsAll(Text.SplitAny([#"fully cleaned text"], " "),{"door", "lock"})) then "lock" else if(Text.Contains([#"fully cleaned text"], "lock")) then "lock" else "" )

 

Thanks in advance.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi,

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

Hope this helps.

14 REPLIES 14
Super User
Super User

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

Hi,

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

Highlighted
Jun_Wang Frequent Visitor
Frequent Visitor

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

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 Smiley Happy

 

query help_exact match.PNG

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

Hi @Jun_Wang,

 

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

 

 

 

 

 

 

 

 

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.

Super User
Super User

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

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)

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

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.

Super User
Super User

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

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 Frequent Visitor
Frequent Visitor

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

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 Smiley Happy

 

 

 

Super User
Super User

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

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

Jun_Wang Frequent Visitor
Frequent Visitor

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

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 Smiley Sad

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 328 members 3,828 guests
Please welcome our newest community members: