This dataset includes a dynamic data source refres...

Samco23 · ‎07-11-2024

Hello,

I am using the API below which refreshed ok in Desktop but is returning an error in Service because I am using dates from a query. Is there a way to modify the API to aviod this error as described in the articles below?

Web.Contents("...URL..."metrics?timespan=" & StartDate & "/" & EndDate & "&interval=PT" & Interval & "&metricnames=" & Metrics & "&top=100&aggregation=" & aggregation & "&api-version=2023-10-01&region=canadacentral&metricNamespace=" & Namespace & "&$filter=Microsoft.ResourceId eq '*'"))

StartDate= Date.ToText(Date.From( Date.AddDays(DateTime.LocalNow(),-14)),"yyyy-MM-ddTHH:mm:ss.000Z")

EndDate= Date.ToText(Date.From( Date.AddDays(DateTime.LocalNow(),+0)),"yyyy-MM-ddTHH:mm:ss.000Z")

Issue described here:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

https://community.fabric.microsoft.com/t5/Power-Query/Dynamic-Data-Source-date/td-p/2379468

Thanks,

Sam

Anonymous · ‎07-11-2024

Hi @Samco23 ,

In the question you have given, you can implement it in desktop but there is no way to implement it in service, in the documentation you have given,

we can try to change your dynamic date to static date to make a try.

let

   // Manually define the start date and end date

   StartDate = "2024-06-28T00:00:00.000Z",

   EndDate = "2024-07-12T00:00:00.000Z",

URL = "...URL...metrics?timespan=" & StartDate & "/" & EndDate & "&interval=PT" & Interval & "&metricnames=" & Metrics & "&top=100&aggregation=" & aggregation &

"&api-version=2023-10-01&region=canadacentral&metricNamespace=" & Namespace & "&$filter=Microsoft.ResourceId eq '*'",

    Source = Web.Contents(URL)

in

    Source

If you have any other questions, you can check out the link I sent you to possibly help you with what you need.

Web.Contents - PowerQuery M | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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