none
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:

    let
        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)
    in
        #"mytable.Connection"

    here is the excel file 

    https://docs.google.com/spreadsheets/d/1tA7IUBbca-Vy5tV9Dr5plCk8i4otMUgrYj03kJLNyjc/edit?usp=sharing

    Thank you for any help!

    Thursday, December 31, 2015 11:28 PM

Answers

  • 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":

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

    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:

    let
        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"})
    in
        #"Expanded Custom"


    Imke Feldmann TheBIccountant.com

    Friday, January 1, 2016 9:13 AM
    Moderator