title: A Better way to Extract Sentences from PDFs using Power Query/BI
body: <p>I have been exploring the idea of using Power BI to dynamically try to extract information from any PDF if given a URL.</p>
  <p>In this case the URL is <a href="https://hpvchemicals.oecd.org/ui/handler.axd?id=fae8d1b1-406b-4287-8a05-f81aa1b16d3f" rel="nofollow noreferrer">https://hpvchemicals.oecd.org/ui/handler.axd?id=fae8d1b1-406b-4287-8a05-f81aa1b16d3f</a> which is a safety assessment profile for formaldehyde. I wish to be able to extract as many sentences from a PDF document as possible.</p>
  <p>Assuming &quot;. &quot;  identifies the end of a sentence this actually works really well where paragraphs are concerned, along with some other trickery, to split the entire PDFinto sentences which I can then search.</p>
  <p>M Code (updated for improved Extraction):</p>
  <pre><code>    let
      
      Source = Pdf.Tables(Web.Contents(&quot;https://hpvchemicals.oecd.org/ui/handler.axd?id=fae8d1b1-406b-4287-8a05-f81aa1b16d3f&quot;), [Implementation=&quot;1.3&quot;]),
      #&quot;Expanded Data&quot; = Table.ExpandTableColumn(Source, &quot;Data&quot;, {&quot;Column1&quot;, &quot;Column2&quot;, &quot;Column3&quot;, &quot;Column4&quot;, &quot;Column5&quot;, &quot;Column6&quot;, &quot;Column7&quot;, &quot;Column8&quot;, &quot;Column9&quot;, &quot;Column10&quot;, &quot;Column11&quot;, &quot;Column12&quot;, &quot;Column13&quot;, &quot;Column14&quot;, &quot;Column15&quot;, &quot;Column16&quot;, &quot;Column17&quot;, &quot;Column18&quot;, &quot;Column19&quot;, &quot;Column20&quot;, &quot;Column21&quot;, &quot;Column22&quot;, &quot;Column23&quot;, &quot;Column24&quot;, &quot;Column25&quot;, &quot;Column26&quot;, &quot;Column27&quot;, &quot;Column28&quot;, &quot;Column29&quot;, &quot;Column30&quot;, &quot;Column31&quot;, &quot;Column32&quot;, &quot;Column33&quot;, &quot;Column34&quot;, &quot;Column35&quot;, &quot;Column36&quot;, &quot;Column37&quot;, &quot;Column38&quot;, &quot;Column39&quot;, &quot;Column40&quot;, &quot;Column41&quot;, &quot;Column42&quot;}, {&quot;Column1&quot;, &quot;Column2&quot;, &quot;Column3&quot;, &quot;Column4&quot;, &quot;Column5&quot;, &quot;Column6&quot;, &quot;Column7&quot;, &quot;Column8&quot;, &quot;Column9&quot;, &quot;Column10&quot;, &quot;Column11&quot;, &quot;Column12&quot;, &quot;Column13&quot;, &quot;Column14&quot;, &quot;Column15&quot;, &quot;Column16&quot;, &quot;Column17&quot;, &quot;Column18&quot;, &quot;Column19&quot;, &quot;Column20&quot;, &quot;Column21&quot;, &quot;Column22&quot;, &quot;Column23&quot;, &quot;Column24&quot;, &quot;Column25&quot;, &quot;Column26&quot;, &quot;Column27&quot;, &quot;Column28&quot;, &quot;Column29&quot;, &quot;Column30&quot;, &quot;Column31&quot;, &quot;Column32&quot;, &quot;Column33&quot;, &quot;Column34&quot;, &quot;Column35&quot;, &quot;Column36&quot;, &quot;Column37&quot;, &quot;Column38&quot;, &quot;Column39&quot;, &quot;Column40&quot;, &quot;Column41&quot;, &quot;Column42&quot;}),
      #&quot;Filtered Rows&quot; = Table.SelectRows(#&quot;Expanded Data&quot;, each ([Kind] = &quot;Page&quot;)),
      #&quot;Removed Columns1&quot; = Table.RemoveColumns(#&quot;Filtered Rows&quot;,{&quot;Name&quot;, &quot;Kind&quot;}),
      #&quot;Added Index&quot; = Table.AddIndexColumn(#&quot;Removed Columns1&quot;, &quot;Index&quot;, 0, 1, Int64.Type),
      Exclude={&quot;Id&quot;},
      List=List.Difference(Table.ColumnNames(#&quot;Removed Columns1&quot;),Exclude),
      MergeAllColumns= Table.AddColumn(#&quot;Added Index&quot;,&quot;Custom&quot;, each Text.Combine(Record.ToList( Table.SelectColumns(#&quot;Added Index&quot;,List){[Index]}), &quot;  &quot;)),
      #&quot;Removed Other Columns&quot; = Table.SelectColumns(MergeAllColumns,{&quot;Id&quot;, &quot;Custom&quot;}),
      #&quot;Split Column by Delimiter&quot; = Table.ExpandListColumn(Table.TransformColumns(#&quot;Removed Other Columns&quot;, {{&quot;Custom&quot;, Splitter.SplitTextByDelimiter(&quot;#(lf)&quot;, QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), &quot;Custom&quot;),
      #&quot;Replaced Value&quot; = Table.ReplaceValue(#&quot;Split Column by Delimiter&quot;,&quot;. &quot;,&quot;.. #&quot;,Replacer.ReplaceText,{&quot;Custom&quot;}),
      #&quot;Split Column by Delimiter1&quot; = Table.ExpandListColumn(Table.TransformColumns(#&quot;Replaced Value&quot;, {{&quot;Custom&quot;, Splitter.SplitTextByDelimiter(&quot;. &quot;, QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), &quot;Custom&quot;),
      #&quot;Cleaned Text&quot; = Table.TransformColumns(#&quot;Split Column by Delimiter1&quot;,{{&quot;Custom&quot;, Text.Clean, type text}}),
      #&quot;Added Index1&quot; = Table.AddIndexColumn(#&quot;Cleaned Text&quot;, &quot;Index&quot;, 0, 1, Int64.Type),
      #&quot;Added Custom&quot; = Table.AddColumn(#&quot;Added Index1&quot;, &quot;Custom.1&quot;, each if Text.Contains([Custom], &quot;#&quot;) then [Index] else null),
      #&quot;Filled Down&quot; = Table.FillDown(#&quot;Added Custom&quot;,{&quot;Custom.1&quot;}),
      #&quot;Grouped Rows&quot; = Table.Group(#&quot;Filled Down&quot;, {&quot;Custom.1&quot;}, {{&quot;Custom&quot;, each Text.Combine([Custom], &quot; &quot;), type text}}),
      #&quot;Split Column by Delimiter2&quot; = Table.ExpandListColumn(Table.TransformColumns(#&quot;Grouped Rows&quot;, {{&quot;Custom&quot;, Splitter.SplitTextByDelimiter(&quot;. &quot;, QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), &quot;Custom&quot;),
      #&quot;Added Custom1&quot; = Table.AddColumn(#&quot;Split Column by Delimiter2&quot;, &quot;Custom.2&quot;, each if Text.Contains([Custom], &quot;NOAEL&quot;) then [Custom] else null)
  in
      #&quot;Added Custom1&quot;
  </code></pre>
  <p>So from this:</p>
  <p><a href="https://i.stack.imgur.com/3otOC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3otOC.png" alt="enter image description here" /></a></p>
  <p>to this:</p>
  <p><a href="https://i.stack.imgur.com/2QTSt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2QTSt.png" alt="enter image description here" /></a></p>
  <p>Although this sentence splitting works pretty well using &quot;. &quot; I'm now being greedy and wondering if this can be done in an even better way.</p>
  <p>There are some instances where the Sentence doesn't split correctly which could be improved. For example, if the end of a sentence is joined to the next e.g. <code>Hello.How are you</code> would not split. Whilst <code>...e.g. Apple</code> itself is recognised would split into. <code>...e.g.</code> and <code>Apple</code>.</p>
  <p><a href="https://stackoverflow.com/questions/25735644/python-regex-for-splitting-text-into-sentences-sentence-tokenizing">Python - RegEx for splitting text into sentences (sentence-tokenizing)</a></p>
  <p>Appears to propose doing this using regex however with this <a href="https://stackoverflow.com/questions/57547297/regexp-in-power-query-using-javascript">excel regex replace function</a> this doesn't appear to work.</p>
  <p>fnRegexExtr3 (doesn't require <code>\\</code> just <code>\</code>):</p>
  <pre><code>    // regexReplace
  let   regexReplace=(text as nullable text,pattern as nullable text,replace as nullable text, optional flags as nullable text) as text =&gt;
      let
          f=if flags = null or flags =&quot;&quot; then &quot;&quot; else flags,
          l1 = List.Transform({text, pattern, replace}, each Text.Replace(_, &quot;\&quot;, &quot;\\&quot;)),
          l2 = List.Transform(l1, each Text.Replace(_, &quot;'&quot;, &quot;\'&quot;)),
          t = Text.Format(&quot;&lt;script&gt;var txt='#{0}';document.write(txt.replace(new RegExp('#{1}','#{3}'),'#{2}'));&lt;/script&gt;&quot;, List.Combine({l2,{f}})),
          r=Web.Page(t)[Data]{0}[Children]{0}[Children],
          Output=if List.Count(r)&gt;1 then r{1}[Text]{0} else &quot;&quot;
      in Output
  in regexReplace
  </code></pre>
  <p>please feel free to look at the PDF link and propose any suggestions for improving the capturing of data.</p>
  <p>I will continue to update here with any progress.</p>