what is the syntax for exact match in Text.Contains("LMS800", [Document])|Power Query|Excel Forum|My Online Training Hub

place a space before and after the text, however only one found don't know why ?

Hi Treacy,   in my real data source is not so "structured" meaning,  the project number can appear anywhere in a verbatim,  so there is no way to split it until so clean,  that was why I use the function match that uses Text.Contains( )

Chris Yap said
Hi Catalin,  thanks for your tips,  this works for Project number infront with blank

for this type Document 898 LS(T)800/2890DOC,   it will not work,  does it mean that I need to add a  "/" to original value, that will be tedious

if there any other workaround

Thank you !  

W

Catalin Bombea said
There is a way to use regular expressions to match complex patterns in power query, if you have messy data.

Here is the function I am using:

<br />
<br />
(Text as text, Pattern as text)=&gt;<br />
let<br />
RunScript = Web.Page(<br />
""<br />
),<br />
Matches = try RunScript{0}[Data]{0}[Children]{1}[Children]{0}[Text] otherwise null<br />
in<br />
Matches<br />
<br />

The query is simple, just take the text from a table, the pattern from another table and pass them to the function:

<br />
<br />
let<br />
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],<br />
Pattern = Excel.CurrentWorkbook(){[Name="Pattern"]}[Content]{0}[Pattern],<br />
#"Added Custom" = Table.AddColumn(Source, "Matches", each FindMatches([Text],Pattern))<br />
in<br />
#"Added Custom"<br />
<br />

You can specify in the pattern lots of conditions like: text can be at the beginning, at the end, might be delimited by some chars, and lots of complex scenarios. 

Catalin Bombea said
There is a way to use regular expressions to match complex patterns in power query, if you have messy data.

Here is the function I am using:

<br />
<br />
(Text as text, Pattern as text)=&gt;<br />
let<br />
RunScript = Web.Page(<br />
""<br />
),<br />
Matches = try RunScript{0}[Data]{0}[Children]{1}[Children]{0}[Text] otherwise null<br />
in<br />
Matches<br />
<br />

The query is simple, just take the text from a table, the pattern from another table and pass them to the function:

<br />
<br />
let<br />
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],<br />
Pattern = Excel.CurrentWorkbook(){[Name="Pattern"]}[Content]{0}[Pattern],<br />
#"Added Custom" = Table.AddColumn(Source, "Matches", each FindMatches([Text],Pattern))<br />
in<br />
#"Added Custom"<br />
<br />

You can specify in the pattern lots of conditions like: text can be at the beginning, at the end, might be delimited by some chars, and lots of complex scenarios.  

wow Catalin,  this is too complicated for you,  that is simple for you

if you have time appreciate you can help to code it in my example and I will learn from there,    just like previously I learned the Text function on search substrings from you,  very useful

at the meant time will use replace "/" with ""  haha

wow Catalin,  amazing,  can explain the pattern in layman term,  really want to learn from you