Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Unable to schedule refresh of dynamic data source

 I am unable to set a sheduled refresh for Azure DevOps

RenePro_0-1678269953208.png

 

RenePro_1-1678270051383.png

 

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"
Status: Investigating

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

Comments
cpwebb
Microsoft Employee

Yes, it does look like authentication is the problem. You'll need to follow the instructions in the link in my last post on how to implement an OAuth flow.

 

Chris