Integrating PowerShell with PowerBI: Module, scrip...

AlexHolmeset · ‎11-21-2017

In my last blog post, Integrating PowerShell with Power BI (Part 1), I set up the connection between Power BI and PowerShell. If you followed the steps in part one, you are set to start using this newly created integration. In this post I will take a look at how we use the Power BI module and how to visualize the data in Power BI reports.

What we will look at in this post:

What you can do with the module.
Writing a reporting script that send data to Power BI.
Create report from dataset in Power BI.

What can the module do?

The Cmdlets you have available are:
Connect-PowerBi
Switch-PowerBIContext
Add-PowerBIDataSet
Get-PowerBIDataSets
Get-PowerBITables
Update-PowerBITableSchema
Add-PowerBIRows
Remove-PowerBIRows
Get-PowerBIGroups
New-PowerBIDataSet
New-PowerBITable
New-PowerBIColumn

With these Cmdlets, you can create and edit a spreadsheet. To read more in detail and see more examples take a look at the "code" for the Power BI module here:
https://www.powershellgallery.com/packages/Microsoft.PowerBI.PowerShell/1.2/Content/Microsoft.PowerB...

Write a reporting script to send data to Power BI

I have two versions of the script. One is for setup and one is for updating data. The script collects data about Office 365 Groups, Owners and Members and publish them to Power BI.

Setup version of the script:
The setup part of the script collects data from Office 365 and creates a data set in Power BI where we can put our data.

#Remember to import Exchange Online module and connect.

$Username = "admin@m365x992073.onmicrosoft.com"
$Password = "**********"

#Define $info array
$info = @()

#Get all groups
$Groups = Get-UnifiedGroup | Select-Object Alias,Accesstype,ManagedBy,PrimarySmtpAddress,Displayname,Notes,GroupMemberCount,GroupExternalMemberCount,WhenChanged

foreach($Group in $Groups) {

    Write-Host -Object "Number of Groups left to process $GroupsCount" -ForegroundColor Green
    $Members = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType members
    $Owners = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType owners
    $MembersCount = $Members.count
    $OwnerCount = $Group.ManagedBy

    foreach($Owner in $Owners){
        $Object=[PSCustomObject]@{
            Name = $Group.Displayname
            Group = $Group.Alias
            Email = $Group.PrimarySmtpAddress
            UserName = $Owner.name
            NumberOfMembers = $Group.GroupMemberCount
            MemberOrOwner = 'Owner'
            NumberOfOwners = $OwnerCount.count
            GroupType = $Group.AccessType
            ExternalMemberCount = $Group.GroupExternalMemberCount
            WhenChanged = $Group.WhenChanged | Get-Date -Format 'yyyy.MM.dd hh:mm'
            Description = $Group.Notes
            }#EndPSCustomObject
        $info+=$object
    }

    foreach($Member in $Members){
        $Object=[PSCustomObject]@{
            Name = $Group.Displayname
            Group = $Group.Alias
            Email = $Group.PrimarySmtpAddress
            UserName = $Member.name
            NumberOfMembers = $Group.GroupMemberCount
            MemberOrOwner = 'Member'
            NumberOfOwners = $OwnerCount.count
            GroupType = $Group.AccessType
            ExternalMemberCount = $Group.GroupExternalMemberCount
            WhenChanged = $Group.WhenChanged | Get-Date -Format 'yyyy.MM.dd hh:mm'
            Description = $Group.Notes
            }#EndPSCustomObject
        $info+=$object
    }

    $GroupsCount--

}

#Connects to PowerBI
Connect-PowerBI -AuthorityName m365x992073.onmicrosoft.com -ClientId 'fa7af8a7-56ad-429f-8f57-76b1bd2087e1'  -UserName $username -Password $password
###

#Groups reporting data.
#Defines collums in the table you are going to create.

$col1 = New-PowerBIColumn -ColumnName Name -ColumnType String
$col2 = New-PowerBIColumn -ColumnName Group -ColumnType String
$col3 = New-PowerBIColumn -ColumnName Email -ColumnType String
$col4 = New-PowerBIColumn -ColumnName UserName -ColumnType String
$col5 = New-PowerBIColumn -ColumnName NumberOfMembers -ColumnType Int64
$col6 = New-PowerBIColumn -ColumnName MemberOrOwner -ColumnType String
$col7 = New-PowerBIColumn -ColumnName NumberOfOwners -ColumnType Int64
$col8 = New-PowerBIColumn -ColumnName GroupType -ColumnType String
$col9 = New-PowerBIColumn -ColumnName ExternalMemberCount -ColumnType Int64
$col10 = New-PowerBIColumn -ColumnName WhenChanged -ColumnType DateTime
$col11 = New-PowerBIColumn -ColumnName Description -ColumnType String

#Creates table from defined collums.
#Comment out after first time setup.
$table1 = New-PowerBITable -TableName GroupReport -Columns $col1,$col2,$col3,$col4,$col5,$col6,$col7,$col8,$col9,$col10,$col11

#Creates dataset from defined table.
#Comment out after first time setup.
$dataset = New-PowerBIDataSet -DataSetName GroupReport -Tables $table1

#Adds dataset and get datasetid.
#Comment out after first time setup.
$datasetid = Add-PowerBIDataSet -DataSet $dataset

#Take note of datasetid so you have it for when you are updating the dataset.
$datasetid

#Remove commeting after firsttime setup on line below.
#Remove-PowerBIRows -DataSetId datasetid -TableName $table1

#Set datasetid manualy after first time setup
Add-PowerBIRows -DataSetId $datasetid -TableName GroupReport -Rows $info

Update version of the script:
This part updates the dataset with new data.

#Remember to import Exchange Online module and connect.

$username="admin@m365x992073.onmicrosoft.com"
$password = "*********" #Enter your password here

#Define $info array
$info = @()

#Get all groups
$Groups = Get-UnifiedGroup | Select-Object Alias,Accesstype,ManagedBy,PrimarySmtpAddress,Displayname,Notes,GroupMemberCount,GroupExternalMemberCount,WhenChanged

foreach($Group in $Groups) {

    Write-Host -Object "Number of Groups left to process $GroupsCount" -ForegroundColor Green
    $Members = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType members
    $Owners = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType owners
    $MembersCount = $Members.count
    $OwnerCount = $Group.ManagedBy

    foreach($Owner in $Owners){
        $Object=[PSCustomObject]@{
            Name = $Group.Displayname
            Group = $Group.Alias
            Email = $Group.PrimarySmtpAddress
            UserName = $Owner.name
            NumberOfMembers = $Group.GroupMemberCount
            MemberOrOwner = 'Owner'
            NumberOfOwners = $OwnerCount.count
            GroupType = $Group.AccessType
            ExternalMemberCount = $Group.GroupExternalMemberCount
            WhenChanged = $Group.WhenChanged | Get-Date -Format 'yyyy.MM.dd hh:mm'
            Description = $Group.Notes
            }#EndPSCustomObject
        $info+=$object
    }

    foreach($Member in $Members){
        $Object=[PSCustomObject]@{
            Name = $Group.Displayname
            Group = $Group.Alias
            Email = $Group.PrimarySmtpAddress
            UserName = $Member.name
            NumberOfMembers = $Group.GroupMemberCount
            MemberOrOwner = 'Member'
            NumberOfOwners = $OwnerCount.count
            GroupType = $Group.AccessType
            ExternalMemberCount = $Group.GroupExternalMemberCount
            WhenChanged = $Group.WhenChanged | Get-Date -Format 'yyyy.MM.dd hh:mm'
            Description = $Group.Notes
            }#EndPSCustomObject
        $info+=$object
    }

    $GroupsCount--

}

#Connects to PowerBI
Connect-PowerBI -AuthorityName m365x992073.onmicrosoft.com -ClientId 'fa7af8a7-56ad-429f-8f57-76b1bd2087e1'  -UserName $username -Password $password

#Removes old data before updating with new data.
Remove-PowerBIRows -DataSetId '025bc397-b8a2-4a3e-bca1-7502b4e50e33' -TableName 'GroupReport'

#Adds new data.
Add-PowerBIRows -DataSetId '025bc397-b8a2-4a3e-bca1-7502b4e50e33' -TableName 'GroupReport' -Rows $info

Create report from dataset in Power BI

Go to https://powerbi.microsoft.com and sign in.
Go to the My Workspace drop down list, select Datasets and under the Actions heading click the graph icon to Create Report.
Select the Stacked Column Chart under Visualizations.
While the chart is still selected, check Group and NumberOfMembers under Fields.
Select Count under Value.
Deselect the chart, and expand UserName under Fields. Now you get a table with all users that are members of any of your Office 365 Groups.
Select a name in the UserName table and you see the magic of Power BI. You can now see in the "Stacked column chart" what groups the selected user is a member of.
When you have run the update part of the script, just press refresh in the upper right corner to get the new data. This is a simple way to get data from any PowerShell source and create a report with that data in Power BI.