Export list of ADO users to Power BI

[KervBruce]

How can I get a list of ADO users, along with their email addresses, from Azure DevOps to Power BI?

I am using Analytics Views to query work items and although this includes various people columns such as 'created by' and 'assigned to', it is just names and does not include the email address.  I want the address to be able to distinguish which organisation a team member is from.  I don't really want to create and maintain a separate manual table with the overhead and risk of human error typos that brings.

Many thanks

[v-jingzhang · Accepted solution]

Hi @KervBruce 

Connecting using Analytics Views doesn't support that. You can connect using Advanced functions VSTS.AccountContents and query ADO REST APIs to get user email accounts. 

First query Teams - Get All Teams - REST API to get the team id and project id you want to query from. You may need to modify the last step to get the team data you want. 

let
    // Teams - Get All Teams
    // GET https://dev.azure.com/{organization}/_apis/teams?api-version=7.1-preview.3
    // This query is to get the team id and project id
    Source = VSTS.AccountContents("https://dev.azure.com/xxxxxxxxxxx/_apis/teams?api-version=7.1-preview.3"),
    #"Imported JSON" = Json.Document(Source,65001),
    value = #"Imported JSON"[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Column1 = #"Converted to Table"{0}[Column1]
in
    Column1

Then query Teams - Get Team Members With Extended Properties - REST API to get team members' display name, id and uniqueName (email account) from the team. 

let
    // Teams - Get Team Members With Extended Properties
    // GET https://dev.azure.com/{organization}/_apis/projects/{projectId}/teams/{teamId}/members?api-version=7.1-preview.2
    
    Source = VSTS.AccountContents("https://dev.azure.com/xxxx/_apis/projects/xxxxxxxxxxx/teams/yyyyyyyyyyyyyyyy/members?api-version=7.1-preview.2"),
    #"Imported JSON" = Json.Document(Source,65001),
    value = #"Imported JSON"[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"identity"}, {"identity"}),
    #"Expanded identity" = Table.ExpandRecordColumn(#"Expanded Column1", "identity", {"displayName", "id", "uniqueName"}, {"displayName", "id", "uniqueName"})
in
    #"Expanded identity"

Hope this helps. 

Reference: Connect using Power Query & Azure DevOps functions - Azure DevOps | Microsoft Docs

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[KervBruce]

Thank you @v-jingzhang That's really helpful and just what I needed.