Forum Discussion
Web.Contents with dynamic date query
Hi Anonymous
You can create a list with below codes to get the date of last monday dynamically. I name this query as DateOfLastMonday.
let Source = Date.ToText(Date.From(Date.AddDays(Date.StartOfWeek(DateTime.LocalNow(),Day.Monday),-7)),"dd/MM/yyyy"), #"Converted to List" = {Source} in #"Converted to List"Then create a query parameter with above list. I name it as Parameter_lastMonday.
"22/03/2021" meta [IsParameterQuery=true, ExpressionIdentifier=DateOfLastMonday, Type="Text", IsParameterQueryRequired=true]At last, use the parameter to replace the fixed date string in your original query.
let AuthKey = "removed", LimitUsed = Number.ToText(Limit), reportPath = Text.Combine({"/datasets/37/feedback?limit=",LimitUsed}), Source = Json.Document(Web.Contents("removed", [Headers=[#"content-type"="application/json", #"x-auth-token"=AuthKey], RelativePath = reportPath, Query = [date_from = Parameter_lastMonday]]))You can refer to this post for how to create the list and parameter.
Daily data refresh from the same file - Microsoft Power BI Community
Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi Anonymous
You can create a list with below codes to get the date of last monday dynamically. I name this query as DateOfLastMonday.
let
Source = Date.ToText(Date.From(Date.AddDays(Date.StartOfWeek(DateTime.LocalNow(),Day.Monday),-7)),"dd/MM/yyyy"),
#"Converted to List" = {Source}
in
#"Converted to List"
Then create a query parameter with above list. I name it as Parameter_lastMonday.
"22/03/2021" meta [IsParameterQuery=true, ExpressionIdentifier=DateOfLastMonday, Type="Text", IsParameterQueryRequired=true]
At last, use the parameter to replace the fixed date string in your original query.
let
AuthKey = "removed",
LimitUsed = Number.ToText(Limit),
reportPath = Text.Combine({"/datasets/37/feedback?limit=",LimitUsed}),
Source = Json.Document(Web.Contents("removed", [Headers=[#"content-type"="application/json", #"x-auth-token"=AuthKey], RelativePath = reportPath, Query = [date_from = Parameter_lastMonday]]))
You can refer to this post for how to create the list and parameter.
Daily data refresh from the same file - Microsoft Power BI Community
Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi v-jingzhang Thanks for this solutions you posted . I had a similar request where i would like pass dates only from M - Friday ,
for now , i would like to test for one day and was following your steps , the place where am stuck is with where i would need to replace the fixed date string in my original query with "Parameter_Monday",. I tried the below and it failed ...
Am using anonymous connection (via Post method) to extract data from DWH Vendor (Web api) and the M - query looks something like this :
Here is the M Query :
***********************
url = "ABC", (the URL does not have a date )
authKey = "authkey ",
content = "{
"FeedType"" : "summary"
"outputformat": "CSV",
"filters":
{
"FROM_EFFECTIVE_DATE": "Parameter_Monday",
"ENTITY_ID": "I(XYZ)",
"includenull": "Y",
}"
,
header = [#"Authorization"=authKey,
#"Content-Type"="application/text"],
Source = Web.Contents(url,
[ Headers = header,
Content = Text.ToBinary(content),
),
CSVDocument = Csv.Document(Source, [Delimiter = ",", Columns = 100, Encoding = 1252, QuoteStyle = QuoteStyle.None]),
#"Promoted headers" = Table.PromoteHeaders(CSVDocument, [PromoteAllScalars = true])
in
#"Promoted headers"
appreciate your help