Regex function to split paragraphs into sentences for Power query

user_13562186 0
Viewed 143

I am attempting to split an example paragraph into sentences using regex in Power Query:

Mr. and Mrs. Smith bought cheapsite.com for 1.5 million dollars, i.e. he paid a lot for it. Did he mind? Dr. Adam Jones Jr. thinks he didn't. In any case, this isn't true... Well, with a probability of .9 it isn't.However, this line wont do it. Qr. Test for Website.COM and Labs.ORG looks good.Creatively not working. t and finished. 9 to start

Into:

Mr. and Mrs. Smith bought cheapsite.com for 1.5 million dollars, i.e. he paid a lot for it.

Did he mind? Dr. Adam Jones Jr. thinks he didn't.

In any case, this isn't true...

Well, with a probability of .9 it isn't.

However, this line wont do it.

Qr.

Test for Website.

COM and Labs.

ORG looks good.

Creatively not working. t and finished.

9 to start

Here is a function that enables PQ to utilise regex replace:

FnRegexReplace

// regexReplace
    let   regexReplace=(text as nullable text,pattern as nullable text,replace as nullable text, optional flags as nullable text) as text =>
        let
            f=if flags = null or flags ="" then "" else flags,
            l1 = List.Transform({text, pattern, replace}, each Text.Replace(_, "\", "\\")),
            l2 = List.Transform(l1, each Text.Replace(_, "'", "\'")),
            t = Text.Format("<script>var txt='#{0}';document.write(txt.replace(new RegExp('#{1}','#{3}'),'#{2}'));</script>", List.Combine({l2,{f}})),
            r=Web.Page(t)[Data]{0}[Children]{0}[Children],
            Output=if List.Count(r)>1 then r{1}[Text]{0} else ""
        in Output
    in regexReplace

I also have the following regex provided prom a previous post which appears to work on Regex101.

https://regex101.com/r/WEC0M9/6

Pattern: (?<!Mr|Mrs|Dr|Jr)(\.+)(\s+(?![a-z])|(?=[A-Z]))

Replace: $1\r\n (I think this can be anything like *)

flags: gm

The issue I have is that when I attempt this is Power Query I am returned with no result:

enter image description here

Alternatively (?<!\w\.\w.)(?<![A-Z][a-z]\.)(?<=\.|\?)\s can be found here but the same issue occurs.

The issue appears to lie with the look-backwards and look-forward respectively ? as the function at least returns a result when this is removed. If anyone can advice on how to best get this paragraph to split using regex as shwon above in PQ that would be great.

M Code: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY9BSwNBDIX/yqPnJVRBiicRetBCD1LBQ+1hdid1Qmcmy0zWpf/e2aLgMXnv5X05Hlf7QnDZY18q4ZDEAnqdvoJhCOzGKsY0aMJZC+7oAUliFM3wGqMrtYMQEwJjdOLhENVuXjHCtm2akiT7J2xb0bN3CTvNXLFrowXJl7pYvPj8Oa3X95sWe82N6IrBVe4WT4XUPxVWJiYifHCMHeaF12Es2rteotgVegY9tvp/IXrRmb+5/F6LkhmzZmtP3DjfGss7V1udTj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "FnRegexReplace", each FnRegexReplace([Column1], "(?<!\w\.\w.)(?<![A-Z][a-z]\.)(?<=\.|\?)\s", "$1\r\n", "gm"))
in
    #"Invoked Custom Function"

Update1: M Code with proposed Regex:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY9BSwNBDIX/yqPnJVRBiicRetBCD1LBQ+1hdid1Qmcmy0zWpf/e2aLgMXnv5X05Hlf7QnDZY18q4ZDEAnqdvoJhCOzGKsY0aMJZC+7oAUliFM3wGqMrtYMQEwJjdOLhENVuXjHCtm2akiT7J2xb0bN3CTvNXLFrowXJl7pYvPj8Oa3X95sWe82N6IrBVe4WT4XUPxVWJiYifHCMHeaF12Es2rteotgVegY9tvp/IXrRmb+5/F6LkhmzZmtP3DjfGss7V1udTj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "FnRegexReplace", each FnRegexReplace([Column1], "((?:\S+\.(?:net|org|com)\b|\b[mdjs]rs?\.|\d*\.\d+|[a-z]\.(?:[a-z]\.)+|[^?.!])+(?:[.?!]+|$))[?!.\s]*)", "$1\n", "gi"))
in
    #"Invoked Custom Function"
powerbi powerquery excel regex
2 Answers

I think the following will be helpfull:

enter image description here

  • For demonstration purposes I loaded the data directly from Excel. I'm sure you can figure out how to connect your PDF;

  • Since the JavaScript-based function is a small HTML-script we have to escape the apostrope in the sample text first using a replace function. Otherwise it will clash with the apostrophes used to write the script in the function (see below). If we don't the function will error out/show nothing. Apostrophe will be shown correctly after applying function;

  • I edited the pattern to catch a full sentence in 1st capture group and for this sample I replaced what is captured with the backreference to this group and a pipe-symbol to visualize the result. Note there is no use of a negative lookbehind nomore since that is not supported in the engine. This resulted in a lengthy pattern which probably does not yet catch all the quirks possible:

    \s*((?:\b[MDJS]rs?\.|\d*\.\d+|\S+\.(?:com|net|org)\b|[a-z]\.(?:[a-z]\.)+|[^.?!])+(?:[.?!]+|$))

M-Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Kol", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","'","&apos",Replacer.ReplaceText,{"Kol"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Replaced Value", "fnRegexReplace", each fnRegexReplace([Kol], "\\s*((?:\\b[MDJS]rs?\\.|\\d*\\.\\d+|\\S+\\.(?:com|net|org)\\b|[a-z]\\.(?:[a-z]\\.)+|[^.?!])+(?:[.?!]+|$))", "$1|"))
in
    #"Invoked Custom Function"

Used function fnRegexReplace:

(x,y,z)=>
let 
   Source = Web.Page(
                     "<script>var x="&"'"&x&"'"&";var z="&"'"&z&
                     "'"&";var y=new RegExp('"&y&"','gmi');
                     var b=x.replace(y,z);document.write(b);</script>")
                     [Data]{0}[Children]{0}[Children]{1}[Text]{0}
in 
   Source

An online demo of the regular expression.

user_97581940

This regex works for most texts from the start but can accommodate for issues that may arise.

\s*((?:\b(?:[djms]rs?|flam|liq|st)\.|\b(?:[a-z]\.){2,}|\.\d|\.(?:com|net|org)\b|[^.?!])+(?:[.?!]+|$)) (Gmi as flags)

Where flam|liq|st are examples where a split would normally occur if the word is followed by a . e.g. for an abbreviation. This section of the regex forced these to be ignored. e.g. If you had the text, St. Bernards typically weigh 80kg. This would usually split on the St. However adding st to this region of the regex ignores this so the sentence is captured as a whole. You can keep adding to this section to try and accommodate for most errors. If you come up with anyway of improving on this further do please post a comment/answer.

user_135621860
Related