Log In
Register
Home
August 21, 2019
Offline
Hi,
What is the Syntax for Text.Contains M code in Power Query
i.e. LMS800 = LMS800, LMS800C4 = LMS800C4
If Document = "....................LMS800C4.........." then return LMS800C4 exactly instead of two outputs (See my attachment)
Thank you!
See AnswerAugust 21, 2019
Offline
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 !
August 21, 2019
Offline
place a space before and after the text, however only one found don't know why ?
July 16, 2010
Offline
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
August 21, 2019
Offline
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( )
Trusted Members
Moderators
November 1, 2018
Offline
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?
November 8, 2013
Offline
Your Match function needs a minor tweak, just add a space after the Document text:
each Text.Contains(Text & " ",[Project])
Answers Post
August 21, 2019
Offline
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 !
November 8, 2013
Offline
The solution is in front of you 🙂
Select the Document column, replace "/" with a space, before using the Match function.
November 8, 2013
Offline
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:
(Text as text, Pattern as text)=>
let
RunScript = Web.Page(
"<script>
document.write(
"""&Text&""".match(
new RegExp('"&Pattern&"','g')
).join(';')
);
</script>"
),
Matches = try RunScript{0}[Data]{0}[Children]{1}[Children]{0}[Text] otherwise null
in
Matches
The query is simple, just take the text from a table, the pattern from another table and pass them to the function:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Pattern = Excel.CurrentWorkbook(){[Name="Pattern"]}[Content]{0}[Pattern],
#"Added Custom" = Table.AddColumn(Source, "Matches", each FindMatches([Text],Pattern))
in
#"Added Custom"
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.
August 21, 2019
Offline
Chris Yap said
Hi Catalin, thanks for your tips, this works for Project number infront with blankfor 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)=><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)=><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
November 8, 2013
Offline
This is the pattern I used:
[A-Z]{2,3}\\(?[A-Z]{1}\\)?[0-9]{3}([A-Z]{1})?([0-9]{1})?
File attached.
August 21, 2019
Offline
wow Catalin, amazing, can explain the pattern in layman term, really want to learn from you
November 8, 2013
Offline
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 (?)
1 Guest(s)
