Web.Contents with dynamic date query

[Anonymous]

Hi, I have created the below query to pull data from a large datasource showing only data from last Monday;

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 = "22/03/2021"]]))

The query works, however I currently have to update the "date_from" parameter every Monday before providing the new file to end users. Is there a way of changing this from a fixed date to a dynamic date so that users can just refresh the data themselves?

I have already tried using #FilteredRows, however the data source is so large the query will repeatedly timeout and end users will spend most of the day just hitting refresh.

[v-jingzhang · Accepted solution]

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.

[Anonymous]

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 

[ERD]

Hello @Anonymous ,

I'm not sure about your datasource and all the options, but, please, have a look at this article: Only Get the Last Few Periods of Data into Power BI using Power Query Filtering.

It's describing the process of filtering the data table BEFORE loading it into the memory.