"Dataset includes a dynamic data source" Dynamic date in Web Source (No API)

[Anonymous]

I can't refresh dataset once it's been published because of this error. I cannot seem to find a workaround that suits my needs.

The below example is grabbing a table from the web successfully in power BI desktop. Publishing the dataset works fine but cannot be refreshed from there without manually refreshing it in desktop and then re-publishing. I would like to be able to schedule refreshes without having to open PBI.

Source = Web.Contents("https://www.example.com/data/type/date:"&Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd"))

I get the typical "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed." error

Can anyone help?

[PhilipTreacy]

@Anonymous 

Hard to say without seeing all of the code.

Phil

[Anonymous]

Hi Phil,

This is the code grabbing the table directly from the webpage:

let
Source = Web.BrowserContents("https://www.example.com/data/type/date:2023-06-26"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.table.table-bordered.table-condensed.table-hover.table-fixed.pre-date-details-list > * > TR > :nth-child(1)"}, {"Column2", "TABLE.table.table-bordered.table-condensed.table-hover.table-fixed.pre-date-details-list > * > TR > :nth-child(2)"}, {"Column3", "TABLE.table.table-bordered.table-condensed.table-hover.table-fixed.pre-date-details-list > * > TR > :nth-child(3)"}}, [RowSelector="TABLE.table.table-bordered.table-condensed.table-hover.table-fixed.pre-date-details-list > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Datetime", type datetime}, {"Category", type text}, {"Release", type text}})
in
    #"Changed Type"

Changes as per your recommendation:

let
Origen = Json.Document(
    Web.Contents(
        "https://www.example.com/data/type/date:", 
        [RelativePath=Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")]
    )
),
    #"Extracted Table From Html" = Json.Document(Origen, {{"Column1", "TABLE.table.table-bordered.table-condensed.table-hover.table-fixed.pre-date-details-list > * > TR > :nth-child(1)"}, {"Column2", "TABLE.table.table-bordered.table-condensed.table-hover.table-fixed.pre-date-details-list > * > TR > :nth-child(2)"}, {"Column3", "TABLE.table.table-bordered.table-condensed.table-hover.table-fixed.pre-date-details-list > * > TR > :nth-child(3)"}}, [RowSelector="TABLE.table.table-bordered.table-condensed.table-hover.table-fixed.pre-date-details-list > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Datetime", type datetime}, {"Category", type text}, {"Release", type text}})
in
    #"Changed Type"

This returns  DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.

[PhilipTreacy]

Hi @Anonymous 

Can you confirm by trying to access that URL in your browser, that it's actually returning JSON?

The error is saying you are getting HTML returned, instead of JSON.

Regards

Phil

[Anonymous]

It is an HTML webpage.

[PhilipTreacy]

Hi @Anonymous 

The root URL can't be dynamic, it needs to be a fixed string.  You can supply dynamic parts of the URL using RelativePath like this

Origen = Json.Document(
    Web.Contents(
        "https://www.example.com/data/type/date:", 
        [RelativePath=Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd"]
    )
),

regards

Phil

[Anonymous]

Hi Phil, 

Thanks for your response, I'm running into a new issue with that - "Expression.SyntaxError: Token ',' expected."

Any ideas, on how to resilve that?

[slorin]

Hi

...[RelativePath=Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")])),

Stéphane 

[Anonymous]

Hi Stéphane, 

Thanks, I tried this and got this new error: 

DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.