Integrating PowerShell with PowerBI: Module, script and reporting (Part 2)

Alexander Holmeset Uncategorized

So now if you read part one, you are set to go starting using this newly created integration. (If not read part one here.)

What we will take a 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 PowerBI.

 

What can the module do?

The commandlets you have availible 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

To simplify, what you can do with these commandlets is to create and edit a spreadsheet.
To read more in detail and see more examples take a look at the “code” for the PowerBI module here:
https://www.powershellgallery.com/packages/Microsoft.PowerBI.PowerShell/1.2/Content/Microsoft.PowerBI.PowerShell.psm1

 

Writing a reporting script that send data to Power BI.

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

 

Setup
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 = "[email protected]"
$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
This part updates the dataset with new data.


#Remember to import Exchange Online module and connect.

$username="[email protected]"
$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

<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>
<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>


Create report from dataset in PowerBI.

  1. Go to https://powerbi.microsoft.com and sign in.
  2. Press My Workspace, Datasets and Create Report.
    create report.png
  3. Select “Stacked Column chart” under Visualizations.
  4. While the chart is still selected, check Group and NumberOfMembers under Fields.
  5. Select Count under value.
    Number of members.png
  6. Deselect the chart, and press UserName under Fields. Now you get a table with all users thats member of any of your Office 365 Groups.
    usernames.png
  7. Now by selecting a name in the UserName table, 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.
    magic.png
  8. 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 PowerBI.

In the next part i will go trough how you setup this script as a scheduled task in Azure Automation.

6 thoughts on “Integrating PowerShell with PowerBI: Module, script and reporting (Part 2)”

  3. Great article!

    I am trying to Connect-Powerbi with a -groupid parameter in order to store the dataset in a group workspace instead of the me workspace. This gives a (401) unauthorized error message. Do you have any idea how this could be performed?

    Thanks again!

    Like

    Reply

      • My bad. The userid used in the script didn’t have PowerBI Pro license. After adding PRO license I am able to publish a dataset to a defined GroupId.
        The next pitstop is to figure out a better authentication in order to leave the user credentials out of the script.

        Like

Leave a reply to Alexander Holmeset Cancel reply