Forum Discussion

Laveezai's avatar
Laveezai
Frequent Visitor
1 year ago

Connect to multiple azure devops organization to retrieve projects

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 f...
Need Help
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
1 year ago

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's avatar
    Laveezai
    Frequent Visitor
    1 year ago

    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.