Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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! 🙂
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! 🙂
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.