Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |