Connect to a devops custom query

Hi  dev85 ,

This is the related document, you can view this content:

https://community.powerbi.com/t5/Desktop/Unable-to-connect-to-Azure-Data-explorer-from-Power-BI-Desktop/td-p/2023533

https://stackoverflow.com/questions/65201453/how-can-i-connect-powerbi-to-queries-in-azure-devops#:~:text=You%20can%20not%20establish%20a%20direct%20connection%20between,connect%20to%20this%20custom%20Analytics%20view%20in%20PowerBI.

Databricks Spark DirectQuery using SQL query - Microsoft Power BI Community

Best Regards,

Liu Yang

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

Instead of linking it to a query in Azure DevOps, could you use the oData.Feed instead in PowerQuery, e.g:

let
Source = OData.Feed ("https://analytics.dev.azure.com/{org}/{project}/_odata/v3.0-preview/WorkItems?"
&"$filter=WorkItemType eq 'Task' "
&"and State ne 'Closed' "
&"and startswith(Area/AreaPath,'{areaPath}') "
&"&$select=WorkItemId,Title,WorkItemType,State"
&"&$expand=Links( "
&"$filter=LinkTypeName eq 'Parent' "
&"and TargetWorkItem/WorkItemType eq 'Product Backlog Item'; "
&"$select=LinkTypeName; "
&"$expand=TargetWorkItem($select=WorkItemType,WorkItemId,Title,State) "
&") "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]),
#"Expand Links" = Table.ExpandTableColumn(Source, "Links", {"LinkTypeName", "TargetWorkItem"}, {"Link Type", "Links.TargetWorkItem"}),
#"Expand Parent Items" = Table.ExpandRecordColumn(#"Expand Links", "Links.TargetWorkItem", {"WorkItemId", "Title", "WorkItemType", "State"}, {"Parent WorkItemId", "Parent Title", "Parent WorkItemType", "Parent State"}) 
in
#"Expand Parent Items"

And then filter out the values where "Link Type" is null (null would mean orphaned, i.e no Parent link).

The result would look something like this: