Solved: Removing HTML Tags and extracting text

Anonymous · ‎08-10-2020

Hey guys,

I need help figuring out how to remove/clean HTML data. I need to just extract the text. I'll copy a sample below for your information.

Thanks in advanced.

p

<div class="ExternalClass5796EA3CBFB24C2DA402D911F488833D">Document Agresso Pipeline 2019-Q2 PID.   Sue scheduled to work Monday next week and perhaps a couple of other days (tbc).?<p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">AP127 - LATCo   <p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing&#5...

<div class="ExternalClassC964E2629C2C4CD7B480E1C98C6DE34C"><p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">Prepare Project Board slides in readiness for meeting to be held on 20/03/2019. <p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">Arrange Sue's work schedule.<p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line...

<div class="ExternalClassE55DE38454154EFEB55A531968AB8AC7">ICT Team will be conducting Year-End activities next week so the time available to be spent on Agresso Pipeline activities will be limited.  Session arranged with Phil to handover all BA related Agresso Pipeline activities and to obtain a status update. AP127 - LATCo   Meeting arranged for 26/03/2019. Review a...

<div class="ExternalClassDB3FBF1C1A9F4B8CA432B1895A7EA234">Prepare to start writing an EOP report and associated slides.????? ? </div>

<div class="ExternalClassF8A3A37CEA9346639AA50BB961BDB29E">Once CJI3 available complete EOP slides.<span style="font:400 13px/20.8px "segoe ui","segoe",tahoma,helvetica,arial,sans-serif;text-align:left;color:#444444;text-transform:none;text-indent:0px;letter-spacing:normal;text-decoration:none;word-spacing:0px;display:inline !important;white-space:normal;orphans:2;font-size-adjust:none;font-stretch:normal;float:none;background-color:transparent;">Request CJI3 report once all known costs have been applied.<span style="font:400 13px/20.8px "segoe ui","segoe",tahoma,helvetica,arial,sans-serif;text-align:left;color:#444444;text-transform:none;text-indent:0px;letter-spacing:normal;text-decoration:none;word-spacing:0px;display:inline !important;white-space:normal;orphans:2;font-size-adjust:none;font-stretch:normal;float:none;background-color&#58...

<div class="ExternalClass53ABD8C10790435FA20EA6B3C0658E1B"><p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">Once CJI3 available complete EOP slides.<p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;"><span style="text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style&#...

<div class="ExternalClass18D86FBC3818425FB569E604DBA97FA9">Once CJI3 available complete EOP slides.Request CJI3 report once all known costs have been applied? </div>

<div class="ExternalClass088C5894BE554D8BBFEC053C5FE81D55"><p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">Once CJI3 available complete EOP slides.<p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">Request CJI3 report once all known costs have been applied??  </div>

<div class="ExternalClass9E6533746EAB4AF2BF3FB94E6A65AFED"><p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">Once CJI3 available complete EOP slides.<p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">Request CJI3 report once all known costs have been applied???  </div>

<div class="ExternalClassCA561E6DB1F5409D857A1841B962611B">CR3 documented and submitted for approval. This CR requests that the implementation of CA client is restarted following the same deployment method as for LATCo.CR2 will need to be revised as the licensing costs for CA will change due to a reduced number of client licences being required (6 as opposed to 20).Sue not worked in PCC this week due to other commitments.?First PCC/Lincs Agresso Forum meeting held on 05/03/2019, at which Agresso experiences were shared between PCC and LIns.   <p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;word-spacing:0px;white-space:normal;orphans:2;background-color:transparent;">...

<div class="ExternalClassDBE9541976CE4649B188607B0788AE75"> <span style="font:400 13px/20.8px "segoe ui", segoe, tahoma, helvetica, arial, sans-serif;text-align:left;color:#444444;text-transform:none;text-indent:0px;letter-spacing:normal;text-decoration:none;word-spacing:0px;display:inline;white-space:normal;orphans:2;float:none;background-color:transparent;">Agresso Pipeline 2019-Q2 PID submitted and approved.  Awaiting approval of CR3.Awaiting license costs for CR2, unable to revise CR until license costs provided.Sue has had problems logging onto PCC network remotely. She has continued to undertake contract related pipeline work for Richard McCarthy.  <p style="margin:0px 0px 10px;text-align:left;color:#444444;text-transform:none;line-height:20.8px;text-indent:0px;letter-spacing:normal;font-size:13px;font-style:normal;font-variant:normal;font-we...

<div class="ExternalClass7B68D75BF86F4A86837989FCAFCF3799">Agresso Pipeline 2019-Q2 PID revised, submitted and approved.  CR3 approved. Amended costs for CA licences added to CR2 and revised CR submitted to PMO. Financial Year-End activities commence next week so the time spent on the Agresso Pipeline by the ICT Team will be limited. Project Board meeting held on 20/03/2019. Timesheet for Sue's time on spent on 28/02 approved. AP99 - Combined Authority  Completed enough of the configuration of the&#160...

<div class="ExternalClass217D5FF855074455A8242C61FC5C1EB8">Agresso Pipeline 2019-Q2 PID approved by PCC. Agresso ICT Team undertook financial year-end activities which have restricted the amount of time available to spend on the project. BA handover meeting held with Phil. Amended CR2 presented and approved at Gate meeting on 29/03/2019. AP99 - Combined Authority  CR 2 approved at gate meeting. Initial testing of system taking place. Meeting arranged on 01/04/2019 to discuss testing approach and monitoring requir...

Fowmy · ‎08-10-2020

@Anonymous

Pasted your sample HTML data in PQ using ENTER DATA table option, you can import from your HTML file as Web source.
So the data looks like this

The Added a custom column with following code

=Html.Table([Column1], {{"ExtractedText",":root"}})

Then Expanded the New Column, you get only the text

If you want to combine al the above lines into one CELL, add the following line:

=Text.Combine(#"Expanded Custom"[ExtractedText],"#(lf)")

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

View solution in original post

Anonymous · ‎08-11-2020

@Fowmy That's awesome, thanks for the advice.

I've also discovered the custom Visual HTML Content, which displays HTML Rich text in their natural/intended form. Also works really nicely.

View solution in original post

Fowmy · ‎08-10-2020

@Anonymous

Pasted your sample HTML data in PQ using ENTER DATA table option, you can import from your HTML file as Web source.
So the data looks like this

The Added a custom column with following code

=Html.Table([Column1], {{"ExtractedText",":root"}})

Then Expanded the New Column, you get only the text

If you want to combine al the above lines into one CELL, add the following line:

=Text.Combine(#"Expanded Custom"[ExtractedText],"#(lf)")

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

Anonymous · ‎08-11-2020

@Fowmy That's awesome, thanks for the advice.

I've also discovered the custom Visual HTML Content, which displays HTML Rich text in their natural/intended form. Also works really nicely.

edhans · ‎08-10-2020

Take a look at Chris's post on this. - Removing HTML tags.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Removing HTML Tags and extracting text

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024