How to Create a List of Teams for Processing in Power BI

No OOTB Method, but PowerShell Works

A recent question in the Microsoft Technical Community asked how to export information about the set of teams in a tenant to Power BI. There’s no OOTB method to do this, but it’s an easy task for PowerShell.  The only complicating factor is that you must use cmdlets from both the Teams and Exchange Online PowerShell modules to generate data that you might want to process with Power BI.

Conceptually, the process is straightforward:

1. Get a list of Teams in the tenant.
2. Process each team and extract the necessary data.
3. Store the data.
4. When all teams are processed, write the data to a CSV file.
5. Import the CSV file into Power BI.

Writing the Script

Here’s the script that I used. There’s nothing very complicated here and it’s easy to change the set of properties I choose to export.

# Set up variables and get list of teams....
$Report = [System.Collections.Generic.List[Object]]::new()
$Teams = Get-Team
Write-Host "Reporting" $Teams.Count "teams"
ForEach ($T in $Teams) {
# Fetch information about the team - mostly by getting it from the Unified Group Object
   Write-Host "Processing" $T.DisplayName
   $G = (Get-UnifiedGroup -Identity $T.GroupId | Select GroupMemberCount, GroupExternalMemberCount, PrimarySmtpAddress, AccessType, Classification)
   $Owners = (Get-TeamUser -Role Owner -GroupId $T.GroupId | Select Name)
   $OwnerNames = $Null
   $First = $True
   ForEach ($O in $Owners) {
     If ($First -eq $True) {
       $OwnerNames = $O.Name
       $First = $False}
     Else {
      $OwnerNames = $OwnerNames + ", " + $O.Name }}
   $ReportLine = [PSCustomObject][Ordered]@{
       Team = $T.DisplayName
       Email = $G.PrimarySmtpAddress
       Members = $G.GroupMemberCount
       Guests = $G.GroupExternalMemberCount
       Owners = $OwnerNames
       Access = $G.AccessType
       Classification = $G.Classification }
    $Report.Add($ReportLine) }
$Report | Export-Csv -NoTypeInformation c:\temp\Teams.csv

When the CSV file is ready, you can import it into Power BI and create whatever visualization or report you want there.

We’ll leave it to Power BI experts to figure out what to do next…

---

This is exactly the kind of example of using PowerShell to fill in a gap in Office 365 or solve a real-life problem for a tenant administrator that we like having in the Office 365 for IT Pros eBook, which is why we have over 1,000 examples in the book. If you want to know how to use PowerShell with Teams, go to Chapter 14…