excel powerquery replacevalues based on cell

Question

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;

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

Answer 1

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"

Answer 2

Another solution from Colin Banfield;

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.