Power BI Query to get GitHub Teams using the GitHub REST API

get-teams-query.md

Get GitHub Teams in Power BI

This Power query is a function that lets you retrieve all the teams in a GitHub organization. It makes an API call to get the first 100 teams, and then iterates to the next 100 until there's no more.

You need the following Functions and Data Tables

GetTeams

Purpose: A function to get all the teams in an organization, given the organization's teamsUrl. It makes use of the List teams REST API.

          (TeamsUrl as text) =>,
          let,
              Source = List.Generate(()=>,
              [Result = try Json.Document(Web.Contents(GitHubAPI, [Headers=[Accept="application/vnd.github+json"], RelativePath=TeamsUrl, Query=[per_page="100", page="1"]])) otherwise null, PageNumber=1],,
                each [Result]<>null,,
                each [Result= try Json.Document(Web.Contents(GitHubAPI, [Headers=[Accept="application/vnd.github+json"], RelativePath=TeamsUrl, Query=[per_page="100", page=PageNumber+1]])) otherwise null, PageNumber=[PageNumber]+1],,
                each [Result]),,
              #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
          in,
          #"Converted to Table"
 

Teams

Purpose: This will create a data table, listing all the teams across all the organizations in the Organizations data table. It iterates through every organization, calling the GetTeams() function.

let
    Source = Organizations,
    #"Invoked Custom Function" = Table.AddColumn(Source, "teams", each GetTeams([teams_url])),
    #"Expanded teams" = Table.ExpandTableColumn(#"Invoked Custom Function", "teams", {"Column1"}, {"teams.Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded teams",{{"teams.Column1", "teams"}}),
    #"Expanded teams1" = Table.ExpandListColumn(#"Renamed Columns", "teams"),
    #"Expanded teams2" = Table.ExpandRecordColumn(#"Expanded teams1", "teams", {"name", "slug", "url"}, {"teams.name", "teams.slug", "teams.url"})
in
    #"Expanded teams2"

TeamsCopilotUsage

Purpose: This data table fetches the GitHub Copilot usage metrics for every team listed. It references the Teams data table and makes use of the Get Copilot metrics for a team REST API.

let
    Source = Table.SelectColumns(Teams,{"organization","teams.slug"}),
    #"Added Custom" = Table.AddColumn(Source, "copilot_team_url", each "/orgs/"&[organization]&"/team/"&[teams.slug]&"/copilot/metrics"),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "teams_copilot_details", each GetCopilotDetails([copilot_team_url])),
    #"Expanded teams_copilot_details" = Table.ExpandTableColumn(#"Invoked Custom Function", "teams_copilot_details", {"Column1"}, {"teams_copilot_details.Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded teams_copilot_details",{{"teams_copilot_details.Column1", "teams_copilot_details"}}),
    #"Expanded teams_copilot_details1" = Table.ExpandListColumn(#"Renamed Columns", "teams_copilot_details")
in
    #"Expanded teams_copilot_details1"