Collecting text (not HTML code) from a web page

[Anonymous]

Greetings all! I'm trying to collect and auto-parse some of the data from this web page:

https://solutions.arcgis.com/electric/help/electric-utility-network-foundation/DataDictionary/DataDi...

The mechanism I've found that works best for me is to, in a normal web brower, load the page, then CTRL-A to Select All, then paste into TextPad, save as a text file, and use that file as input to Power Query. I'd like to know if there's a way to get Power Query to do exactly the same thing instead of me doing it manually: give me simply the line-by-line text from the web page to work with. I have tried a lot of different renditions of playing with Web.Contents and Web.BrowserContents, etc., but to no avail! I've also spent a lot of time searching around online for an answer to this question, but I found nothing addressing this specific way of getting text from a site. To be extremely repetitive but say it in a different way: Is there a way to get Power Query to give me the same result as if I Select All on a web page, copy it to the clipboard, and paste it into a Notepad-like program? Thanks for any time you're willing to give to point me in the right direction!

[v-angzheng-msft]

Hi, @Anonymous 

You could try using python scripts to scrape the web text and then get the data from the python script at PowerBI.

I know there is such a way, but I am not familiar with python programming, so I can't give specific scripting suggestions at the moment. You can search the web for relevant information to help you

refer:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for this suggestion! This might indeed be a good path to follow. I appreciate this creative idea!

[lbendlin]

That webpage is too large for Power Query

Here would be an example for a smaller page.

let
    Source = Text.Split(Web.BrowserContents("https://solutions.arcgis.com/electric/help/electric-utility-network-foundation/"),">"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Trimmed Text" = Table.TransformColumns(#"Converted to Table",{{"Column1", Text.Trim, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each not Text.StartsWith([Column1], "<")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter("<", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1.1"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Anonymous]

Thanks very much for your time on this! It unfortunately doesn't quite accomplish what I'm trying to achieve, but, maybe what you're presenting is the closest Power Query is able to get. What I ultimately wanted is the "rendered text" that an end user sees in a web browser, as opposed to the HTML. One example of the difference between what I'm looking for and what your method provides is: Whenever there's a table that you'd see on the rendered page, if I were to copy and paste that table from within the browser into Notepad, every row across would be on one line of text, but in this case, because your code "splits" at the instances of "<" and ">", each cell of a table's row is on its own line. If you have any slick tricks you can think of to achieve my main goal, great, and if not, no problem! Thank you either way!

[lbendlin]

You can use HTML5 Viewer visual to have it render smaller, not too complex pages. Anything else will be way too cramped.