Connect to a devops custom query

Question

Hi everybody!&nbsp;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:&nbsp;&nbsp;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?&nbsp;Thanks in advance for your help.&nbsp;

dev85 · Answer

Hi&nbsp;ferryv&nbsp;Thank you for your reply&nbsp;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.&nbsp;

anonymous · Answer

Hi&nbsp; dev85&nbsp;,
&nbsp;
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
&nbsp;
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.

ferryv · Answer

Instead of linking it to a query in Azure DevOps, could you use the oData.Feed instead in PowerQuery, e.g:&nbsp;letSource = OData.Feed ("https://analytics.dev.azure.com/{org}/{project}/_odata/v3.0-preview/WorkItems?"&amp;"$filter=WorkItemType eq 'Task' "&amp;"and State ne 'Closed' "&amp;"and startswith(Area/AreaPath,'{areaPath}') "&amp;"&amp;$select=WorkItemId,Title,WorkItemType,State"&amp;"&amp;$expand=Links( "&amp;"$filter=LinkTypeName eq 'Parent' "&amp;"and TargetWorkItem/WorkItemType eq 'Product Backlog Item'; "&amp;"$select=LinkTypeName; "&amp;"$expand=TargetWorkItem($select=WorkItemType,WorkItemId,Title,State) "&amp;") ",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).&nbsp;The result would look something like this:

dev85 · Answer

Hi&nbsp;ferryv&nbsp;Thank you for your reply&nbsp;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.&nbsp;

maamirkhan2023 · Answer

Hi All,&nbsp;I try the same thing that you followed but I am gettting error&nbsp;

Forum Discussion

Connect to a devops custom query

Related content

ADF / DevOps Pipeline

Connecting DevOps to PowerBi

pbi and devops

Connect Azure DevOps queries to Power BI

Create Git Credentials for DevOps Repo