excelpowerqueryexcel-web-query

excel powerquery replacevalues based on cell

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

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

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;

herewith the questions:

  1. I want to be able to specify the year in the query using a cell, replacing the myParm with a cell value and refreshing on change (can do it with excel native, need to know how to do it with powerquery)
  2. I want to be able to replace the year value on the resultant year column data with whatever is in the aforementioned cell
Solution

  • For #1, assuming you have an Excel Table named YearTable with a single column named Year and a single detail row with the required year value (e.g. 2015), you can use this M expression:

    Excel.CurrentWorkbook(){[Name="YearTable"]}[Content]{0}[Year]

    This dives into that table and plucks the value from the first detail row.

    For example, you could embed that in your opening Step e.g.

    Web.Page(Web.Contents("http://www.timeanddate.com/holidays/south-africa/" & Number.ToText(Excel.CurrentWorkbook(){[Name="YearTable"]}[Content]{0}[Year])))

    For #2, I would add use that expression to Add a Column using something like this formula:

    [Date] & " " & Number.ToText(Excel.CurrentWorkbook(){[Name="YearTable"]}[Content]{0}[Year])

    Then you can use the Parse button (Transform ribbon, under Date) to convert that to a Date datatype if required.

    Note the generated Change Type step I got from that page cast Date as a Date with an implied year (the issue you noticed). Just edit the formula for that step, to set the "Date" column as "text" to avoid that.

    Here's my entire test M script:

    let
    Source = Web.Page(Web.Contents("http://www.timeanddate.com/holidays/south-africa/" & Number.ToText(Excel.CurrentWorkbook(){[Name="YearTable"]}[Content]{0}[Year]))),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Header", type text}, {"Date", type text}, {"Weekday", type text}, {"Holiday name", type text}, {"Holiday type", type text}}),
    #"Added Derived Date" = Table.AddColumn(#"Changed Type", "Derived Date", each [Date] & " " & Number.ToText(Excel.CurrentWorkbook(){[Name="YearTable"]}[Content]{0}[Year])),
    #"Parsed Date" = Table.TransformColumns(#"Added Derived Date",{{"Derived Date", each Date.From(DateTimeZone.From(_)), type date}})
in
    #"Parsed Date"