Problem with automatic dataset refresh because "dynamic datasource"

[gluizqueiroz]

When I publish my project to Power BI Web, the schedule refresh page show me the following message:

This data set includes a dynamic data source. Since dynamic data sources are not updated in the Power BI service, this data set will not be updated. Learn more: https://aka.ms/dynamic-data-sources.
Data source for Query

I have 1 table that was a M Power Query code to create a table with month/year starting in jan/2020 until today, and 1 column with a "default value" and a columna with "default value" + [LastDay] like the following result:

Year	Month Text	Month Int	LastDay	URL	Query
2020	01	1	2020-31-01	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-31-01
2020	02	2	2020-29-02	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-29-02
2020	03	3	2020-31-03	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-31-03
2020	04	4	2020-30-04	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-30-04
2020	05	5	2020-31-05	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-31-05
2020	06	6	2020-30-06	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-30-06
2020	07	7	2020-31-07	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-31-07
2020	08	8	2020-31-08	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-31-08
2020	09	9	2020-30-09	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-30-09
2020	10	10	2020-31-10	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-31-10
2020	11	11	2020-30-11	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-30-11
2020	12	12	2020-31-12	https://api.mysite.com/public/v1/sales?token=abc123&filter=	createdDate eq 2020-31-12

After that, I use the following M Code to do a request to [URL] + [Query], like the following:

#"New Column2" = Table.AddColumn(#"New Column1", "WebContent", each 
        Json.Document(
            Web.Contents(
                [URL], 
                [ 
                    Query = [#"filter" = [Query]]
                ]
            )
        )
    ),
    #"WebContent Expanded" = Table.ExpandListColumn(#"New Column2", "WebContent"),

The result of previous code is a List (I transform to Record and Expanded to new columns).

I have created this logic to do 1 request per month.

On Power BI Desktop it works fine and refresh correctly.

My problem is when I publish to web and try to schedule a automatic refresh. 

How can I manage this error and can schedule a automatic refresh?

I have searched for a solution and 1 answer say to use the param "Query" on function Web.Contents(), but it not worked.

[collinq]

HI @gluizqueiroz ,

I think, by definition, it won't work.  According to that link in the message "In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service." and "A dynamic data source is a data source in which some or all of the information required to connect cannot be determined until Power Query runs its query, because the data is generated in code or returned from another data source."

That appears to me to be what you doing.  Therefore, I don't think that there is a work-around to get the Service to actually refresh the data for you.  You might go to the Developer forum to see if somebody has an idea for you to rework your query so that you get the output that you want in a less dynamic method.  I know that I have done something similar where I add a flag to my date table something like "current month - yes/no" to make my query not be dynamic.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!