Identify text contains Alphabets&Number

[Anonymous]

Hello everyone,

Can you please help to advise, in power query, how to identify specific format of text?

Here is sample file for download.

After loading data, we would like to add one more column, called "Search Type".

If a Query contains either of following format text, set the value to "Product Search" in the new added Column, otherwise, set value to "non-Product Search":

 - 1 digit alphabet followed by 2-4 digits numbers, or

 - 2-3 digits numbers followed by 1-6 digits alphabets, or

 - 1-3 digits numbers followed by a "-" dash followed by 5-7 digits numbers or/and alphabets, or

 - "Nikon" or "Canon" or "Alienware" contained

Your inputs are appreciat3ed.

Thank you.

H

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

Can you change your mind and look for "non-Product Search" with search term and "Product Search" for others?

If you agree with what I said, then you can create a column.

Like this:

Column = 
 IF(SEARCH("Search Term",[Query],1,-1)>0,"non-Product Search","Product Search")

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If problem still persists, pls let me know.

Best Regards,
Community Support Team _ Janey

[mahoneypat]

This is a job for RegEx, which is not natively supported in Power BI.  However, this article describes a hack to do it.  It is also possible with R or Python.

Power Query Regular Expression Hack | Math Encounters Blog (mathscinotes.com)

Pat

[Anonymous]

Thank you for reply @mahoneypat , I will try to look into it although not a programmer 😉

Have a good day!