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

[jcru1999]

Hello community, 

I hope you are doing well, I got a quick question. I am importing via Workday Web Service a report that in the source data link contains the date as when the report is retriving the information. How can I update this to bring the information as of today, instead of the date the web service link carries? I tried to use paramenters, but I was not able to make it work or set up my parameter to use "Today". 

Here is a sample of the link, 

[rohit_singh · Accepted solution]

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! 🙂

[jcru1999]

Hello Rohit, you are amaizing, it worked perfectly! 

The only thing was that after the "& vDate &" there was missing the "-07" which I think is part of the code and it was misleading as part of the date, once I gate the ""& vDate &"-07....."" it starter to work. 

Thank you!