Extract table from HTML plain text using Power BI

Question

Hi

I am trying to extract value from the table using a power query

Can you help

Sample File

https://tinyurl.com/bdcpetcw

I want to extract information from col C

Answer 1

HI

Sorry I have just started using it recently

No problem at all, you know now :)

OK, let me explain how I did it so you have a chance to do it yourself next time

1 - Copied the first record (row #2 in your Excel file) in [specfication] column

2 - Pasted it into Notepad and saved the file as Text

3 - Created a new Power BI query targeting that Text file

4 - Chose "Fridge Freeze" under HTML Tables > Transform Data

5 - PowerBI generated a few lines of code. With the Advanced Editor I copied what's after #"Extracted Table From Html" = (Html.Table(Source, ....))

6 - Discarded that query

7 - Created a new query targeting the Excel file you shared

8 - Selected "webscrape" > Transform > Data

9 - Removed "(null)" in [specification] (there are a few)

10 - Added a Custom Column & pasted the code saved at #5 then replaced Source with [specification]

After just combined all Tables in the Custom Column

The corresponding PowerBI file is available here (you'll have to change the path to the Excel file - it's currently mine)

NB: The HTML records in your Excel file are very consistent so the code PowerBI generated at #5 seems to work (I only checked a few) for all records. This won't necessarily be the case all the time...

Answer 2

Dear gerry07_799,

Good day! Thank you for posting to Microsoft Community. I am happy to help you.

I have checked you shared sample, and it appears to me that you are trying to extract the data from a html source. Per my experience, the best and easiest way to get the data is through programming languages such as Python rather than Power Query. There are several open source libraries (e.g., BS4) having html parser to make it easy to pull data from complex html source.

In case you would prefer to stick with solution in Power Query, you can consider using multiple html tags as Delimiter to split the original column into multiple columns, and then tranform the data to extract the value you want.

I have extract the heading value as an example. You may follow the same method to extract other value in your source

See highlighted column for extracted heading value

Sample code

let

*Source = Excel.CurrentWorkbook(){[Name="webscrape"]}[Content],* 

*#"Changed Type" = Table.TransformColumnTypes(Source,{{"specification", type text}}),* 

*#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "specification", Splitter.SplitTextByDelimiter("</span>", QuoteStyle.Csv), {"specification.1", "specification.2"}),* 

*#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "specification.1", Splitter.SplitTextByEachDelimiter({">"}, QuoteStyle.None, true), {"specification.1.1", "specification.1.2"}),* 

*#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"specification.1.1", type text}, {"specification.1.2", type text}, {"specification.2", type text}}),* 

*#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"specification.1.2", Text.Trim, type text}})* 

in

*#"Trimmed Text"*

Sincerely,

Ryan | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***

Answer 3

Hi

after step 5 I do not see #"Extracted Table From Html" = (Html.Table(Source, ....))

Answer 4

HI

Sorry I have just started using it recently

Yes this is the result I wanted

Answer 5

Hi gerry07_799

At the bottom of each reply you get there's a link to mark as solution (where appropriate). A very good indicator for those who search this site. According to your history it appears you missed that link a few times so far. It would be highly appreciated you review your previous cases and mark those that deserve it - Thanks in advance

Regarding this case could you confirm the expected result/output looks like this?: