Hi Chris,

There are some suggestions here.

Mynda

Hi Mynda,

what they meant by "wrapping the string text in single spaces (b4 and after). Did the same with the list of lookup values.................."

thank you !

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

Hmmm, because the documents that don't have 'Project' at the end don't have a space after the project code. Why don't you split the Project... off the end of the documents so they're consistent, then it may work better. See attached.

Mynda

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( )

You could add a query that calculates the highest length match for a given document, and then only return the rows that match on both document name and match length?

Your Match function needs a minor tweak, just add a space after the Document text:

each Text.Contains(Text & " ",[Project])

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 !

The solution is in front of you 🙂

Select the Document column, replace "/" with a space, before using the Match function.

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:

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

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.

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

This is the pattern I used:

[A-Z]{2,3}\(?[A-Z]{1}\)?[0-9]{3}([A-Z]{1})?([0-9]{1})?

File attached.

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

If you want to learn regular expressions, you can start here: https://regex101.com/r/wQ4mK9/1

They provide also a playground for JavaScript expressions, used in the power query function.

[A-Z]{2,3}\(?[A-Z]{1}\)?[0-9]{3}([A-Z]{1})?([0-9]{1})?

You can see the groups in different colors (i used 5 groups):

first group says that there can be 2 or 3 uppercase letters

second group says that there can be an uppercase letter in paranthesis, the ? says that it might be missing

third group: 3 digits always

4-th group: there can be an uppercase letter, might me missing on some entries (that's what ? symbol means)

group 5: there can be 1 uppercase letter, but might be missing sometimes (?)