August 21, 2019

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!

Trusted Members

Moderators

November 1, 2018

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

November 8, 2013

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

November 8, 2013

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

November 8, 2013

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

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

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

November 8, 2013

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

November 8, 2013

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)