Power BI - Import data from web

Well, I'm quite familiar with Power Query, but not so much with getting data from the web.

Anyhow, from this topic I learned that you can import a web page as text, and that helped me to come up with a solution.

Please bear with me, it may well be that the selections, filterings and extractions are not full proof, but in this case it all seems to work.

let
    // // Import webpage as text
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://activecaptain.com/quickLists/marinaIndexUSState.php?st=TN&city=Knoxville"))}),

    // Get the row(s) containing "<table": these include the href's to the linked pages
    #"FilteredOn<table" = Table.SelectRows(Source, each Text.Contains([Column1], "<table")),

    // In the next lines, the web addresses are extracted
    SplittedOnhref = Table.TransformColumns(#"FilteredOn<table", {{"Column1", each Text.Split(_, "href"), type text}}),
    Expanded = Table.ExpandListColumn(SplittedOnhref, "Column1"),
    #"FilteredOnNotStart<table" = Table.SelectRows(Expanded, each not Text.StartsWith([Column1], "<table")),
    ExtractedTextAfterFirstQuotes = Table.TransformColumns(#"FilteredOnNotStart<table", {{"Column1", each Text.Middle(_, 1+Text.PositionOf(_,"""")), type text}}),
    ExtractedTextUntilFirstQuotes = Table.TransformColumns(ExtractedTextAfterFirstQuotes, {{"Column1", each Text.Start(_, Text.PositionOf(_,"""")), type text}}),

    // Now get the table from each WebPage
    GetDetailsFromWebPageTable = Table.AddColumn(ExtractedTextUntilFirstQuotes, "Details", each Web.Page(Web.Contents("https://activecaptain.com/quickLists/" &[Column1]))[Data]{0}),

    // Get the friendly marina name
    AddedMarina = Table.AddColumn(GetDetailsFromWebPageTable, "Marina", each [Details][Column2]{0}, type text),

    // Finishing touches
    SelectedColumns = Table.SelectColumns(AddedMarina,{"Marina", "Details"}),
    ExpandedDetails = Table.ExpandTableColumn(SelectedColumns, "Details", {"Column1", "Column2"}, {"Attribute", "Value"}),

    // Add index to sort the data in the data model
    AddedIndex = Table.AddIndexColumn(ExpandedDetails, "Index", 1, 1)
in
    AddedIndex

Websites are very specific, so you can't expect a solution for 1 website to work for any website.

Even worse, you can't expect a solution working today will still be working tomorrow.

My solution was based on the required end result (the URL's for the various marinas), so starting with the end result, you can work your way backwards to the beginning. In the original solution I found that the required URL's could be found at the row containing the string "<table", and I proceeded from there.

So for any website you need to sort how (and if) you can get the required data.

You can find an example in this post for a multi-page website.