cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clf_foreman
Regular Visitor

Web.Contents with dynamic date query

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.

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @clf_foreman 

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.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @clf_foreman 

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.

View solution in original post

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
Solution Sage
Solution Sage

Hello @clf_foreman ,

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors