CODEWITHSUNDEEP
azure-devopspowerbipowerbi-desktop
the_new_guy
the_new_guy

Reputation: 187

How can I connect PowerBi to Queries in Azure Devops?

I need to get he data from the queries of the Azure devops. Im trying to establish a direct connection between them. Im able to access all the other items like Boards,tasks, work items etc. But, im unable to see the Query. How can I rectify this issue?

Thanks in advance.

Upvotes: 2

Views: 9807

Answers (2)

Mahomedalid
Mahomedalid

Reputation: 3124

It is possible. Go to marketplace and install the extension Open in PowerBI:

https://marketplace.visualstudio.com/items?itemName=stansw.vsts-open-in-powerbi

It will add a dropdown link to open your query in PowerBI. Internally the data source does something like this:

let
    url = "https://<myorg>.visualstudio.com",
    collection = "",
    project = "<myproject>",
    team = "<myteam>",
    id = "<query guid>",

    // Create wrapper for VSTS.AccountContents function as a workaround for the static code analysis in Power BI Service.
    contents = (o) => VSTS.AccountContents(
        url, 
        [ 
            Version              = Record.FieldOrDefault(Record.FieldOrDefault(o, "Headers", []), "Referer", ""),
            IsRetry              = Record.FieldOrDefault(o, "IsRetry", false),
            ManualStatusHandling = Record.FieldOrDefault(o, "ManualStatusHandling", {}),
            Query                = Record.FieldOrDefault(o, "Query", []),
            RelativePath         = Record.FieldOrDefault(o, "RelativePath", null),
            Timeout              = Record.FieldOrDefault(o, "Timeout", null)
       ]),

    Source = Functions[WiqlRunFlatWorkItemQueryById](contents, url, [Collection = collection, Project = project, Team = team], id)
in
    Source

Upvotes: 0

Cece Dong - MSFT
Cece Dong - MSFT

Reputation: 31003

Generally, you can pull data from Analytics into Power BI in one of three ways:

  • Connect using the OData queries
  • Connect using the Azure DevOps Data Connector
  • Connect using the Power BI's OData Feed connector

More details, please check the following link:

https://learn.microsoft.com/en-us/azure/devops/report/powerbi/overview?view=azure-devops#supported-data-connection-methods

It seems you are using the second way. This connector only works with Boards data (work items) and does not support other data types. You can not establish a direct connection between Query and PowerBI. But, as the Query is used to list work items based on field criteria you specify, you can create a custom Analytics view in Azure DevOps and add filters by field criteria, then you can connect to this custom Analytics view in PowerBI.

https://learn.microsoft.com/en-us/azure/devops/report/powerbi/analytics-views-create?view=azure-devops

Or you can use OData queries to filter field criteria directly.

Upvotes: 1

Related Questions