Dynamic URL - WEEKDAY

[Anonymous]

Hi All,

I am pulling data from a dynamic URL that is using a date in the string. I have managed to create the parameter to pass over the string and everything appears to work okay. The issues I have are that;

I only want to include the date of the last WEEKDAY in the file path if the refresh time is before 1600hrs on a WEEKDAY or if it is a Saturday or Sunday.

Step 1: = Table.AddColumn(#"Changed Type", "Custom", each Date.From(DateTime.LocalNow())) - 'Creates the Date/Time'

Step 2: = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}}) - 'Transforms the Date/TIme' 

Step 3: = Table.AddColumn(#"Changed Type1", "Custom.1", each DateTime.ToText([Custom],"dd-MMMM-yyyy")) - 'Transforms the Date/TIme to the required text output to pass as the query parameter' 

Step 4: = #"Inserted Day of Week"[Custom.1] - 'the query output parameter'

output parapeter for the URL is named NHS URL = "15-June-2020"

Formed URL = "https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2020/06/COVID-19-total-announced-de..." & #"NHS URL Date" & ".xlsx"

Any thoughts would be really appreciated......

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Here are the codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.LocalNow()),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}}),
    #"Inserted Day of Week" = Table.AddColumn(#"Changed Type1", "Custom.1", each if Date.DayOfWeek([Custom])=0 or Date.DayOfWeek([Custom])=6 or DateTime.Time([Custom])<=#time(16,0,0) then DateTime.ToText(Date.EndOfWeek([Custom]),"dd-MMMM-yyyy") else null),
    #"Formed URL" = if #"Inserted Day of Week"=null then null else
"https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2020/06/COVID-19-total-announced-de..."&List.First( #"Inserted Day of Week"[Custom.1])&".xlsx"
in
    #"Formed URL"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allan,

Many thanks for your very swift response. I maybe should have explained my issue a little clearer......

If I refreshed the report yesterday before 1600hrs the URL should have defaulted to the previous business day (Friday 12-June-2020) as the endpoint data is not published over the weekend.

"https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2020/06/COVID-19-total-announced-de..."

If I was to refresh the report this morning before 1600hrs the dynamic URL would default to the previous business day (Monday 15-June-2020) as the new data is not published until after 1600hrs.

"https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2020/06/COVID-19-total-announced-de..."

If I was to refresh the report today after the 1600hrs rebublication time the dynmaic URL should of course look like this.

"https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2020/06/COVID-19-total-announced-de..."

I hope this explanation makes sense?