Connect to multiple azure devops organization to retrieve projects

[Laveezai]

Hello,

As part of a project, I need to be able to connect to a number of azure devops organizations to retrieve the underlying projects.

At the moment I know how to connect to a project via odata feed or azure devops analytics connector.

But I'd like to be able to import all the projects from several organizations dynamically into power bi.

Has anyone come across this use case before?

Good evening to you 😀

P.S : I need to get projects but also iterations, users, workitems, areas related to all project from every organization

[Sahir_Maharaj]

Hello @Laveezai,

Can you please try creating the following Power Query Function (Transform Data > Advanced Editor):

let
    GetProjectsByOrganization = (Organization as text) as table =>
    let
        URL = "https://dev.azure.com/" & Organization & "/_apis/projects?api-version=6.0",
        
        Source = Json.Document(Web.Contents(URL, [Headers=[Authorization="Basic " & Text.FromBinary(Text.ToBinary("<PAT>"))]])),
        
        Projects = Source[value],

        ProjectsTable = Table.FromList(Projects, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        ExpandedTable = Table.ExpandRecordColumn(ProjectsTable, "Column1", {"id", "name", "description", "state"})
    in
        ExpandedTable
in
    GetProjectsByOrganization

After invoking the function, you will have a nested table for each organization.

Hope this helps!

[Laveezai]

Unfortunately, I get this error message when I try to apply the function for each given organization.

When I tested the url on chrome, I also saw that it only returned the organization's projects. I need to retrieve work items, users, area paths, iterations and underlying projects.