Scheduled refresh on Web.Contents that has calculated date parameter

[mbowler]

I've read a few posts where people have a similar problem but I can't see an obvious solution.

The problem I have is that I need to use a web query to access audit data from our Dynamics 365 system (audit data is not available via Dataverse api), however as there are millions of records I want to just the last months records. The query below works in the PB Desktop and can be manually refreshed on the service but I need this to refresh daily but I'm seeing this error on the service:

The query is this:

let
#"Today" = DateTime.LocalNow(),
#"Today Minus 1 Month" = Date.AddMonths(DateTime.Date(#"Today"),-1),
#"FormattedDate" = Date.ToText(#"Today Minus 1 Month", [Format="yyyy-MM-dd"]),
#"WebQuery" = "https://orgname.crm4.dynamics.com/api/data/v9.2/audits?" & "$select=_objectid_value,objecttypecode,createdon,_userid_value&$orderby=createdon desc" & "&$filter=objecttypecode eq 'myobjectname' and createdon gt " & #"FormattedDate",
Source = Json.Document(Web.Contents(#"WebQuery", [Headers=[Accept="application/json", Prefer="odata.include-annotations=""*"""]])),

All help gratefully appreciated.

[lbendlin]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[mbowler · Accepted solution]

Thanks for this pointer @Anonymous , as per usual the MS documentation didn't quite give the full explanation I needed (ie how to stucture for multiple query paramaters). But I found a couple of really good blogs by Chris Webb that helped me and also explained why splitting the query into component parts is needed for the service.

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

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code (crossjoin.co.uk)

I also had the added complication that my query parameters all started with a $ ($select=), which required me to wrap them in quotes (eg #"$select" = ).

Only problem I have now is that the I can't get the authentication to work on the refresh on the service (says the credentials are invalid even though when I edit the credentials and it tests the connection, it says they've worked).

[Anonymous]

Hi @mbowler ,
Based on your description and the error message provided, this error indicates that the current data source does not fully support scheduled refreshes, which is a common issue when using web queries or APIs that do not fully support scheduled refreshes. According to the code you provided, the above query is a dynamic query, and according to the following documentation,

Troubleshooting unsupported data source for refresh - Power BI | Microsoft Learn

Data refresh in Power BI - Power BI | Microsoft Learn

dynamic data sources do not support scheduled refreshes, and these queries may generate different results on each refresh. You can also check in the power bi desktop data source settings to see if there is this warning,

if the warning is displayed in the Data Source Settings dialog box that appears, it will show dynamic data sources that cannot be refreshed in the Power BI service.

As a possible workaround, you can set up a scheduled export from Dynamics 365 to a supported data source, such as an Azure SQL database or Azure Blob storage, and then connect Power BI to that data source. You also mentioned that your data has millions of rows and dates exist, so you might consider doing an incremental refresh after the above operation, which will benefit your refresh efficiency.

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

Best regards,
Albert He

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