cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How to change data source dates Dynamically on Month end

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

4 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How to change data source dates Dynamically on Month end

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"

to.PNG

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

frrom.PNG

 

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

ur.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

Highlighted
Community Support
Community Support

Re: How to change data source dates Dynamically on Month end

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"

date.PNG

 

Best Regards,

Icey

 

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

View solution in original post

Highlighted
Community Support
Community Support

Re: How to change data source dates Dynamically on Month end

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.

View solution in original post

Highlighted
Helper I
Helper I

Re: How to change data source dates Dynamically on Month end

Hi @Icey ,

 

It worked like this.

Thank you for you help. Really Appreciated.

supriya_13_0-1593935044858.png

 

View solution in original post

10 REPLIES 10
Highlighted
Super User IX
Super User IX

Re: How to changes data source dates Dynamically on Month end

@supriya_13 , Try if this can help

https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: How to changes data source dates Dynamically on Month end

Hi @supriya_13 ,

 

Please refer to these posts:

How to setting a dynamic URL connection;

Dynamic connection to Web URL.

 

 

 

Best Regards,

Icey

 

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

Highlighted
Helper I
Helper I

Re: How to changes data source dates Dynamically on Month end

@Icey @amitchandak Thank you for reply,Solution is nice but I am looking for settings that will run from back end automatically on month end (like schedular ) and no need to open the report, like what we do in normal coding. Something like that to save time (I have a lot of reports which contains 4-5 data source in single report)

Highlighted
Community Support
Community Support

Re: How to change data source dates Dynamically on Month end

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"

to.PNG

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

frrom.PNG

 

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

ur.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

Highlighted
Helper I
Helper I

Re: How to change data source dates Dynamically on Month end

Hi @Icey ,

 

This is really great.

Need 1 more help. I am not able to change the date format in advance query. I want date format ("YYYY-mm-dd") in advance query.

supriya_13_0-1593674342759.png

 

Highlighted
Community Support
Community Support

Re: How to change data source dates Dynamically on Month end

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"

date.PNG

 

Best Regards,

Icey

 

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

View solution in original post

Highlighted
Helper I
Helper I

Re: How to change data source dates Dynamically on Month end

@Icey It worked. this is really awesome. 

Thanks Alot. Really appreciated. 

 

Highlighted
Helper I
Helper I

Re: How to change data source dates Dynamically on Month end

@Icey Schedular is not visible for these reports.  Its giving below error.

supriya_13_0-1593686648444.png

 

Highlighted
Community Support
Community Support

Re: How to change data source dates Dynamically on Month end

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.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors