Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jun_Wang
Helper I
Helper I

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

Hi,

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

15 REPLIES 15
doingmeheadin
Frequent Visitor

A solution that works for me and keeps everything in power query is to put a space at the beginning of your search substring. For example " lock" will pick up all instances where the word "lock" is preceeded by a space " ". 

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 🙂

 

query help_exact match.PNG

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)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 😞

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Really appreciated your help. Here are some text.

 

I cleaned the data from R, so there is no singular and plura problem. My predefined list is shorten the if-else will go through in the order like below:

1) List.ContainsAll({"door","lock"} 

2) List.ContainsAll({"key","lock"}

3) text.contain ("lock", "padlock", "locksmith")

 

The above 1) and 2) will use the nested text.splitany() by space 🙂

 

Many thanks.

 

block toilet haywards ac substation.
wdv supply chain padlock road gate
dvk lock toilet cubicle door portacom smoko sheed missing lock
entry door design hand injury potential due proximaty lock door jamb
sbk southbrook substation - block toilet
isl - break door lock transfield office
investigate blockage drain main driveway
haywards toilet block ac
ay lock mechanism switchyard b man gate accept key
condenser internal door lock ac fall apart
esni lock gate
hot water unit toilet block need replacement leak flood toilet block
wvy - rust screw exterior amenity block
wairaki investigation electronic door lock control door. need replacement
igh - unblock drain

Hi,

Is this the result you are expecting (see the second column)?

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes, I think so. Well, I'm ready to embrace the DAX now. Could you please show me how to do it?

Many thanks!!!!

Hi,

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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 🙂

 

 

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.