Changing a data source Web Service date to run as of Today with Parameters

Hi jcru1999 ,

Please follow the below steps :

1) Create a custom function to change date into "yyyy-mm-dd" format. This is important since this format is used in the web link.

Copy and paste this code into a blank query. I call this function "yyyymmdd"

let 
    param = (parameter as date) =>

    let 
        Source = Date.Year(parameter) * 10000 + Date.Month(parameter) * 100 +  Date.Day(parameter),
        #"Converted to Table" = #table(1, {{Source}}),
        #"Inserted First Characters" = Table.AddColumn(#"Converted to Table", "First Characters", each Text.Start(Text.From([Column1], "en-GB"), 4), type text),
        #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle(Text.From([Column1], "en-GB"), 4, 2), type text),
        #"Inserted Last Characters" = Table.AddColumn(#"Inserted Text Range", "Last Characters", each Text.End(Text.From([Column1], "en-GB"), 2), type text),
        #"Inserted Merged Column" = Table.AddColumn(#"Inserted Last Characters", "Merged", each Text.Combine({[First Characters], [Text Range], [Last Characters]}, "-"), type text),
        #"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column",{"Merged"}),
        Merged = #"Removed Other Columns"{0}[Merged]
    in
        Merged

in param

2) Invoke custom function with today's date. This gives us current date in yyyy-mm-dd format 

3) Replace text in query strng with parameter. Try changing your string to : 

r = Json.Document(Web.Contents("https://............................?Effective date=" & vDate & "%3A00&Worker_Active=0&format=json"))

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂