Unable to schedule refresh of dynamic data source

[Analyst-Rene]

 I am unable to set a sheduled refresh for Azure DevOps

If I manually refresh, I get

Something went wrong

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. Learn more: https://aka.ms/dynamic-data-sources.

Please try again later or contact support. If you contact support, please provide these details.

Cluster URI: WABI-WEST-US-redirect.analysis.windows.net
Activity ID: c834e4e3-0feb-4e81-a718-32b9db6b7e3a
Request ID: 7e133278-88af-251d-3cbf-c913e6366381
Time: 2023-03-08 09:57:46Z

I did look online and have found some articles but still having trouble with the query:

let
Content = "
{
""query"": ""Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.TeamProject] = 'Aha Integration' AND [System.WorkItemType] = 'Feature' AND [State] <> 'Closed' AND [State] <> 'Removed'""
}
",

Response = Web.Contents("https://dev.azure.com/-REMOVED-/Aha%20Integration/_apis/wit/wiql?api-version=7.0
",
[
Content = Text.ToBinary(Content),
Headers=[Authorization="Basic REMOVED=", #"content-Type" = "application/json"]
]
),
Json = Json.Document(Response),
workItems = Json[workItems],
tablefromList = Table.FromList(workItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(tablefromList, "Column1", {"id", "url"}, {"id", "url"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"url", type text}, {"id", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "workItem", each Json.Document(Web.Contents([url],
[
Headers=[Authorization="Basic REMOVED=", #"content-Type" = "application/json"]
]
))),
#"Expanded workItem" = Table.ExpandRecordColumn(#"Added Custom", "workItem", {"fields"}, {"fields"}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Expanded workItem", "fields", {"System.Title", "System.Description", "System.WorkItemType", "Custom.ExecutiveSummary", "Custom.RAG", "Custom.Deliverables"}, {"System.Title", "System.Description", "System.WorkItemType","Custom.ExecutiveSummary", "Custom.RAG", "Custom.Deliverables"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded fields",null," ",Replacer.ReplaceValue,{"System.Description"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null," ",Replacer.ReplaceValue,{"Custom.ExecutiveSummary"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value1", "Description", each Html.Table([System.Description], {{"Description",":root"}})),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "ExecutiveSummary", each Html.Table([Custom.ExecutiveSummary], {{"Executive Summary",":root"}})),
#"Expanded Description" = Table.ExpandTableColumn(#"Added Custom2", "Description", {"Description"}, {"Description.Description"}),
#"Expanded ExecutiveSummary" = Table.ExpandTableColumn(#"Expanded Description", "ExecutiveSummary", {"Executive Summary"}, {"ExecutiveSummary.Executive Summary"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded ExecutiveSummary",{{"id", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "Custom", each OData.Feed("https://analytics.dev.azure.com/-REMOVED-/Aha%20Integration/_odata/v4.0-preview/WorkItems?" & "$filter=WorkItemId eq "& [id] & "&$select=WorkItemId, Title, WorkItemType, State" & "&$expand=Children($select=WorkItemId,Title, WorkItemType, State)", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom3", "Custom", {"Title", "WorkItemType", "State", "Children", "Priority1"}, {"Custom.Title", "Custom.WorkItemType", "Custom.State", "Custom.Children", "Custom.Priority1"}),
#"Expanded Custom.Children" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Children", {"WorkItemId", "Title", "WorkItemType", "State"}, {"Custom.Children.WorkItemId", "Custom.Children.Title", "Custom.Children.WorkItemType", "Custom.Children.State"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.Children",{{"System.Title", "Project"}, {"Description.Description", "Description"}, {"ExecutiveSummary.Executive Summary", "Executive Summary"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom.Title", "Custom.WorkItemType", "Custom.State"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom.Children.WorkItemId", "Risk WorkItemId"}, {"Custom.Children.Title", "Risk Title"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Custom.Children.WorkItemType"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Custom.Children.State", "Risk State"}, {"Custom.RAG", "RAG"}}),
#"Added RAG Note" = Table.AddColumn(#"Renamed Columns2", "RAG Note", each if [RAG] = "On Track" then "Releases on track, good general progress" else if [RAG] = "At Risk" then "Need a note for At Risk" else if [RAG] = "Serious Issue" then "Need a note for Serious Issue" else null),
#"Split Column by Position" = Table.SplitColumn(#"Added RAG Note", "url", Splitter.SplitTextByPositions({0, 46}, false), {"url.1", "url.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"url.1", type text}, {"url.2", type text}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type2", "url.2", Splitter.SplitTextByPositions({0, 7}, true), {"url.2.1", "url.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position1",{{"url.2.1", type text}, {"url.2.2", type text}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type3","c9fe0588-5732-4fd9-a4fb-f7fb918216ac/_apis/wit/workItems","Aha%20Integration/_workitems/edit",Replacer.ReplaceText,{"url.2.1"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value2",{"url.1", "url.2.1", "url.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Item Link"),
#"Removed Columns2" = Table.RemoveColumns(#"Merged Columns",{"System.Description", "Custom.ExecutiveSummary"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"id", "Project ID"}, {"Risk WorkItemId", "Risk ID"}, {"Description", "Project Status"}})
in
#"Renamed Columns3"

[v-yanjiang-msft]

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....

Best regards,

Community Support Team_kalyj

[Analyst-Rene]

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?

[cpwebb]

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-...); 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-d...). This is going to be a very tough problem to solve...

[Analyst-Rene]

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

[Analyst-Rene]

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.

[cpwebb]

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

[Analyst-Rene]

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

[Analyst-Rene]

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

[cpwebb]

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?

[t8ken]

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