Unable to schedule refresh of dynamic data source

Hi Analyst-Rene ,

From the official document can see, 

In most cases, Power BI datasets that use dynamic data sources can't be refreshed in the Power BI service. 

To determine whether your dynamic data source can be refreshed, open the Data Source Settings dialog in Power Query Editor, and then select Data Sources In Current File. In the window that appears, look for the following warning message: Some data sources may not be listed because of hand-authored queries.

If you want to try some workaroud about refreshing dynamic data source, you can refer to the Chris Web's blog:Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin.co.uk)

Best regards,

Community Support Team_kalyj

Thanks v-yanjiang-msft , we will give it a try. The opnly reason we are using this option is becasue the built in Azure DevOps connection doesn't retrieve all the tables we need, for example, custom tables - OData Feed is somehwat limited too. Is there a better way to connect to Azure DevOps?

Hi Rene,

There's a lot going on here and a lot of things that are likely not going to work unless you build a custom connector. For a start it looks like you're trying to make POST requests to a source that needs authentication and you're connecting to a source that uses OAuth2 (?) authentication, both of which require a custom connector (https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-query-power-bi/); and you're calling Web.Contents in a custom column and getting the url to call from another column in the same table (which is a dynamic source - you won't know what the url up front); and you're calling OData.Feed with a dynamic url too (this solution might help: https://blog.crossjoin.co.uk/2023/01/15/using-odata-feed-and-the-query-option-to-avoid-the-dynamic-data-sources-error-in-power-bi/). This is going to be a very tough problem to solve...

Thanks cpwebb , I will take a look at those links. It's a shame it works and I can refresh in Power BI Desktop but not in the Power BI Service - obviously the two work very differently. An easier solution would be for Azure DevOps to enable you to add milti-row text fields to Analytics Views and then you can just use the Azure DevOps (Boards only) data option - I did raise a ticket in hopes https://developercommunity.visualstudio.com/t/Fix-and-Improve-DevOps-integration-and-c/10344751

Appreciate you taking a look

Hi, Just updating this thread @v-yanjiang-msft, @cpwebb - I have still not found a solution to this. Is there somthing that can be done in the Power BI Service? Our query works perfectly well in Power BI Desktop and I don't know why the Service would be different, that it cannot refresh the data in the same way. With our query, when I can click on Refresh within the Power BI Desktop and it refreshes but in the Power BI Service, if I click on Refresh to refresh the dataset - it fails. Have either of you been able to get data from Azure Devops, where their are custom columns and multi row text columns? We have even tried simplifying things in ADO and now only use Feature to track everything, instead of having User Story children.

We won't be able to use contom connectors, I didn't seem to need to from my desktop and the OAuth2 works. Would truly appreciate assistance on this. I tried using just OData but just not getting the fields I need (green, yes - red, no) but I do get them with our query, no problem, except when published to the service.

I haven't tried it myself but I'm very familiar with the problem and I don't think there's anything you can do apart from create a custom connector. Sorry...

Chris

Thanks cpwebb - I am going to use a workaround (useing an Excel addin) and I have posted an idea on the board https://ideas.powerbi.com/ideas/idea/?ideaid=8432a781-2ef3-ed11-a81c-000d3ae52c8f - since I can't use Work Item Query Language (WIQL) either 

Or Web.Content to connect to an ADO Query (even though this page in in a table format) https://ideas.powerbi.com/ideas/idea/?ideaid=86e8a49b-cdf3-ed11-a81c-00224851e41b

Just to update this thread - we tried a custom connector but the same results. This works on Power BI Desktop but not the service

Did you follow the instructions here https://learn.microsoft.com/en-us/power-query/handling-authentication#implementing-an-oauth-flow to create your custom connector? Can you post the code you're using to to call your custom connector?

Hi cpwebb,

I have been looking into this for Analyst-Rene and trying to create a custom connection, and I can get a custom connection working for the Power BI Desktop but not the service, where the refresh says it is not a supported data source.

The end goal is to connect to Azure DevOps and query the wiql (https://dev.azure.com/{org}/{project}/_apis/wit/wiql/) endpoint to return certain workitems, then use the workitem ID's to call the individual work item endpoint (https://dev.azure.com/{org}/{project}/_apis/wit/workItems/{id})

I understand that this will be hard to achieve because of the dynamic building of the URL, so to break it down into smaller chunks I created a very simple custom connector, making a direct call to one of the workitems, and I followed your suggestions of using the relative path field in Web.Contents, like this:

[DataSource.Kind="aha_feature", Publish="aha_feature.Publish"]
shared aha_feature.Contents = (optional message as text) =>
    let
        headers = [
            #"Authorization"="Basic {PAT}",
            #"content-Type" = "application/json"
            ],
        Source = Web.Contents(
        "https://dev.azure.com", 
            [
                RelativePath = "{org}/{project}/_apis/wit/workItems/283750",
                Headers = headers 
            ]
        ),
        json = Json.Document(Source)
    in
        json;

This connector works in VS Code using the Power Query SDK, and in Power BI Desktop, but will not refresh in the Power BI Service.

I noticed your last comment about using OAuth to authenticate, is it possible that the basic authorization is piece that is causing the refresh to fail?

I haven't ever used OAuth, but will try and figure it out and give it a go.

Cheers,

Tim