none
Combining Multiple HTML Sources with the Same Table Name into a Query RRS feed

Answers

  • 1) Create the following function, which I've named GetWellData:

    (url as text, tableNum as number) as table =>
    let
        Source = Web.Page(Web.Contents(url)),
        TableData = Source{tableNum}[Data]
    in
        TableData

    2) In Excel, create a table with the 50 URLs in one column, and the table number in another (this column is optional, in case you want flexibility in specifying the table number - otherwise the number can be hardcoded in the query).

    3) Build a query from the table in step 2.

    4) Add a custom column to the query that references the GetWellData function, e.g.:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "WellData", each GetWellData([URL], [Table Number]))

    5) Expand the table column created in step 4.

    Final code should look like the following (Excel table name is UrlTable - columns are URL and Table Number):

    let
        Source = Excel.CurrentWorkbook(){[Name="UrlTable"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"URL", type text}, {"Table Number", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "WellData", each GetWellData([URL], [Table Number])),
        #"Expanded WellData" = Table.ExpandTableColumn(#"Added Custom", "WellData", {"RPT DATE", "LUW CODE", "STORAGE FAC", "DOC USE", "WELL CNT", "OPENING STK", "OIL PROD(BBL)", "GAS PROD(MCF)", "DISPOSITION", "CLOSING STK", "PARISH"}, {"WellData.RPT DATE", "WellData.LUW CODE", "WellData.STORAGE FAC", "WellData.DOC USE", "WellData.WELL CNT", "WellData.OPENING STK", "WellData.OIL PROD(BBL)", "WellData.GAS PROD(MCF)", "WellData.DISPOSITION", "WellData.CLOSING STK", "WellData.PARISH"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded WellData",{"URL", "Table Number"}),
        #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"WellData.RPT DATE", Order.Ascending}})
    in
        #"Sorted Rows"

    I've included a sort step to sort the date column in ascending order.


    Wednesday, March 30, 2016 11:13 PM