Connect to a devops custom query

[dev85]

Hi everybody!

I'm a BI developer and recently I had to build a dashboard using the boards info that are in our DevOps environment. I was able to load all the boards into power BI with no problem, however our dev team created a query to load the tasks with the respective issue parent. The query appears listed in the queries menu:

Now, when I connect to azure from power BI I can only see the boards, not the queries. Is there a way to connect to the query?

Thanks in advance for your help.

[ferryv]

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:

[dev85]

Hi @ferryv Thank you for your reply

Actually I solve this by creating a flow in Automate that reads the query and move that info to a dataverse table, then I connect that table easy to my Power BI report. Personally I think this approach is much easier than the OData way. 

[dev85]

Hi @ferryv Thank you for your reply

Actually I solve this by creating a flow in Automate that reads the query and move that info to a dataverse table, then I connect that table easy to my Power BI report. Personally I think this approach is much easier than the OData way. 

[maamirkhan2023]

Hi All,

I try the same thing that you followed but I am gettting error

[ferryv]

Hi. This looks like a permission issue in Azure DevOps for that specific project. See also: https://learn.microsoft.com/en-us/azure/devops/report/powerbi/odataquery-connect?view=azure-devops

[Anonymous]

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

https://stackoverflow.com/questions/65201453/how-can-i-connect-powerbi-to-queries-in-azure-devops#:~....

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.