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.
- Go to https://powerbi.microsoft.com and sign in.
- Press My Workspace, Datasets and Create Report.
- Select “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 press UserName under Fields. Now you get a table with all users thats member of any of your Office 365 Groups.
- 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.
- 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.
[…] a look at how to automate the process of updating the data in the Power BI report we created in part 2, without any need of a computer/server. We will manage to do this by scheduling a Runbook in Azure […]
LikeLike
[…] This is a improvement of my reporting script from my Power BI blog series. Read these two blog posts to better understand this script and article: Integrating PowerShell with PowerBI (Part 1) Integrating PowerShell with PowerBI: Module, script and reporting (Part 2) […]
LikeLike
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!
LikeLike
Are you able to connect using Me workspace?
LikeLike
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.
LikeLike
Send me a DM on Twitter, and il help you out 🙂
LikeLike