How to change data source dates Dynamically on Month end

[supriya_13]

Hi All,

Is there any way to change/update Data source dates (from date - to date) dynamically every month end to fetch data?

e. g. http://api.xyz.com?CreatedDateFrom=2020-04-01&CreatedDateTo=2020-06-30

[Icey · Accepted solution]

Hi @supriya_13 ,

How about create 2 queries in Power Query Editor, and then use relative path, like:

let CreatedDateTo  = Date.EndOfMonth(DateTime.LocalNow()),
    #"Extracted Date" = Date.From(CreatedDateTo)
in
    #"Extracted Date"

let CreatedDateFrom  = Date.AddMonths(Date.EndOfMonth(DateTime.LocalNow()),-2),
    #"Extracted Date" = Date.From(CreatedDateFrom)
in
    #"Extracted Date"

Then, change your data source in Advance Editor like this:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey · Accepted solution]

Hi @supriya_13 ,

Try this:

let CreatedDateFrom  = Date.AddMonths(Date.EndOfMonth(DateTime.LocalNow()),-2),
    #"Extracted Date" = Date.From(CreatedDateFrom),
    #"FormatDate" = Date.ToText(#"Extracted Date","yyyy-MM-dd")
in
    #"FormatDate"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey · Accepted solution]

Hi @supriya_13 ,

Oh, I find the cause:

the problem is that the Power BI service wants to validate the URI before it commits to refreshing the data source. Because the URI isn’t static, there is no URI to validate.

Try something like this:

Web.Contents(
    "http://api.xyz.com", 
    [
        Query=
        [
            CreatedDateFrom=CreatedDateFrom, 
            CreatedDateTo=CreatedDateTo
        ]
    ]
)

Reference: 

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code;

Web.Contents(), M Functions And Dataset Refresh Errors In Power BI.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.