none
web query cell input parameter and date conversion RRS feed

  • Question

  • Web query:

    Using excel previously I had to fiddle with the query file and use the ["Ticker"] placeholder in order to get the parameters button enabled so I could specify a cell as the input value, with refresh on change.

    Now with power query I'm a little lost.

    I'm extracting data from a site using excel powerquery, the site is www.timeanddate.com/holidays/south-africa/2014

    The web table presents dates in the format mmm dd so;

    • Jan      01
    • Mar      20
    • Mar      21 ...etc.

    To get results for different years I can invoke a prompt to request year input and replace the relevant value in the URL as follows;

    = let    #"Table 0" = (myParm)=>let    Source = Web.Page(Web.Contents("http://www.timeanddate.com/holidays/south-africa/" & Number.ToText(myParm))),

    However - without the year specified in the web results table, when imported into excel it understandably plonks its own values in (Excel native just uses current year being 2015, powerquery interprets the info completely differently) alla such;

    • 2001/01/01     
    • 2020/03/01     
    • 2021/03/01

    herewith the questions:

    1. I want to be able to specify      the year in the URL query parameter using a cell value
    1. I want to be able to replace the year value on the resultant year column data with whatever is in the aforementioned cell
    Friday, September 4, 2015 6:49 PM

Answers

  • 1) In Excel, create a table with Year as the column name and enter the year as the row value. Then create a query from the table. Your query should have one column and one row value. Name the query appropriately and save.

    2) Get the data from the web site. Assume we name the query HolidayTable. Convert the query to a function query e.g.

    (Year as number)=>
    let
        Source = Web.Page(Web.Contents("www.timeanddate.com/holidays/south-africa/"&Number.ToText(Year))),
        Data0 = Source{0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Header", type text}, {"Date", type date}, {"Weekday", type text}, {"Holiday name", type text}, {"Holiday type", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Header"})
    in
        #"Removed Columns"

    3) Add this function as a new column in the step (1) query, and add a new date custom column. After a couple other transformations (column reorder, column removal), you should end up with a query that looks like the following:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each HolidayTable([Year])),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date", "Weekday", "Holiday name", "Holiday type"}, {"Date", "Weekday", "Holiday name", "Holiday type"}),
        #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Calendar Date", each #date([Year],Date.Month([Date]),Date.Day([Date]))),
        #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Year", "Date", "Calendar Date", "Weekday", "Holiday name", "Holiday type"}),
        #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Year","Date"})
    in
        #"Removed Columns"

    Notes:

    a) The first two lines are from the original table query in step (1).

    b) The #"Added Custom" step adds a new custom column, which passes the value in the Year column to the HolidayTable function

    c) The #"Added Custom1" step adds a new custom column that creates a new date from the value in the Year column, and the month and day values from the original Date column.

    Friday, September 4, 2015 11:53 PM