Expand HTML Links from Excel Table in Query Editor

EnochS,

There is no data when I open your HTML link. However, you can perform the following steps to get data from all the links in Query Editor.

1. Split the Project Bidding column to get the number. This step generates the following code, you can copy it to the Advanced Editor of your query.

#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Projects Bidding", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Projects Bidding.1", "Projects Bidding.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Projects Bidding.1", type text}, {"Projects Bidding.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Projects Bidding.1", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Projects Bidding.1.1", "Projects Bidding.1.2"}),

2. Add a new blank query and paste the following code in the Advanced Editor of the blank query. And rename the query to fnGetByid.

let
fnGetByid = (urlid as text) =>
let
    Source = Web.Page(Web.Contents(Text.Replace("http://www.bxwa.com/bxwa_toc/private/{id}/toc.html","{id}", urlid))),
    Data0 = Source{0}[Data]
in
    Data0
in
fnGetByid

3. Add a custom column in the Data table which is imported from the Excel file, then expand the custom column.

=fnGetByid([Projects Bidding.1.2])

Reference:
https://kzhendev.wordpress.com/2014/04/14/scraping-the-web-with-power-query/

Regards,
Lydia