Web Query Help RRS feed

  • Question

  • Hello, I am trying to write a web query in M (Power Query) language to extract html tables from a list of url's in excel workbook worksheet.  Here is what I have so far, which is not much since I am a newbie:

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"List of Url's to query", type text}}),
        #"Set mytable" = Sheet1.QueryTables("myquery"),
        #"mytable.Connection" = "URL;" & Sheets ("Sheet1") (Column1)

    here is the excel file

    Thank you for any help!

    Thursday, December 31, 2015 11:28 PM


  • Easiest way to create a function is to perform the task once and then replace the Parameters that shall Change by a placeholder. Then you just have to add the "function initiator" in front:()=> and put the parameters into the brackets. Name this query "fnWebCall":

        Source = Web.Page(Web.Contents(URL)),
        Data0 = Source{0}[Data]

    You start the main query as you did from your table that contains the URLs and call the function by adding a column and pass the Name of your URL-column as a parameter:

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChgType = Table.TransformColumnTypes(Source,{{"List of Url's to query", type text}}),

        #"Added Custom" = Table.AddColumn(ChgType, "Custom", each fnWebCall([List of Url's to query])),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Due Date", "Solicitation Title", "Location"}, {"Due Date", "Solicitation Title", "Location"})
        #"Expanded Custom"

    Imke Feldmann

    Friday, January 1, 2016 9:13 AM