microsoft / microsoft/powerbi-powershell

BadRequest error when adding data rows

Open
#170 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
413
Forks
134
Avg merge
33m
Merged PRs (30d)
1

Description

Using modified code from here: https://alexholmeset.blog/2018/02/11/office-365-groups-reporting-and-power-bi-visualisation/

I receive the following error on only the last two lines of code adding of data rows no matter what I do to the dataset...

Exception : Microsoft.Rest.HttpOperationException: Operation returned an invalid status code 'BadRequest'
at Microsoft.PowerBI.Api.V2.Datasets.d__11.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Api.V2.DatasetsExtensions.d__75.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Api.V2.DatasetsExtensions.PostRows(IDatasets operations, String datasetKey, String tableName, Object requestMessage)
at Microsoft.PowerBI.Common.Api.Datasets.DatasetsClient.AddRows(String datasetId, String tableName, List1 rows, Nullable1 workspaceId)
at Microsoft.PowerBI.Commands.Data.AddPowerBIRow.ExecuteCmdlet()
at Microsoft.PowerBI.Commands.Common.PowerBICmdlet.ProcessRecord()
TargetObject : Microsoft.PowerBI.Commands.Data.AddPowerBIRow
CategoryInfo : WriteError: (Microsoft.Power...a.AddPowerBIRow:AddPowerBIRow) [Add-PowerBIRow], HttpOperationException
FullyQualifiedErrorId : Operation returned an invalid status code 'BadRequest',Microsoft.PowerBI.Commands.Data.AddPowerBIRow
ErrorDetails :
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at , : line 2
PipelineIterationInfo : {0, 1}
PSMessageDetails :

Code:
`$GroupsInfo = @()
$NumberofGroups = @()
$TeamsCount = 0
$NumberofOwners = @()
$NumberOfMembers = @()

$WarningDate = (Get-Date).AddDays(-90)
$WarningEmailDate = (Get-Date).AddDays(-365)
$Today = (Get-Date)
$Date = $Today.ToShortDateString()

$Groups = Get-UnifiedGroup | Select-Object Alias,Accesstype,ManagedBy,PrimarySmtpAddress,Displayname,Notes,GroupMemberCount,GroupExternalMemberCount,WhenChanged,SharePointSiteUrl
$GroupsCount = $Groups.count
foreach($Group in $Groups) {

Write-Host -Object "Number of Groups left to process $GroupsCount" -ForegroundColor Green
Start-Sleep -Seconds 2

#Write-Host "Checking Group:" $Group.DisplayName
$ObsoleteReportLine = $Group.DisplayName
$SPOStatus = "Normal"
$SPOActivity = "Document library in use"
$NumberWarnings = 0
$NumberofChats = 0
$TeamChatData = $Null
$LastItemAddedtoTeams = "No chats"
$MailboxStatus = $Null

Fetch information about activity in the Inbox folder of the group mailbox

$Data = (Get-MailboxFolderStatistics -Identity $Group.Alias -IncludeOldestAndNewestITems -FolderScope Inbox)
$LastConversation = $Data.NewestItemReceivedDate
$NumberConversations = $Data.ItemsInFolder
$MailboxStatus = "Normal"

If ($Data.NewestItemReceivedDate -le $WarningEmailDate)
{
#Write-Host "Last conversation item created in" $Group.DisplayName "was" $Data.NewestItemReceivedDate "-> Could be Obsolete?"
$ObsoleteEmailGroups = $ObsoleteEMailGroups + 1
$ObsoleteReportLine = $ObsoleteReportLine + " Last conversation dated: " + $Data.NewestItemReceivedDate + "."
$NumberWarnings++
}
Else
{ # Some conversations exist - but if there are fewer than 20, we should flag this...

If ($Data.ItemsInFolder -lt 20)
{
$ObsoleteReportLine = $ObsoleteReportLine + " Only " + $Data.ItemsInFolder + " conversation items found."
$MailboxStatus = "Low number of conversations found"
$NumberWarnings++
}
Else
{

Write-Host $Group.DisplayName "has" $Data.ItemsInFolder "size of conversation items: " $Data.FolderSize

}
}

Loop to check SharePoint document library

If ($Group.SharePointDocumentsUrl -ne $Null)
{
$SPOSite = (Get-SPOSite -Identity $Group.SharePointDocumentsUrl.replace("/Shared Documents", ""))
$AuditCheck = $Group.SharePointDocumentsUrl + "/*"
$AuditRecs = 0
$AuditRecs = (Search-UnifiedAuditLog -RecordType SharePointFileOperation -StartDate $WarningDate -EndDate $Today -ObjectId $AuditCheck -SessionCommand ReturnNextPreviewPage)
If ($AuditRecs -eq $null)
{
#Write-Host "No audit records found for" $SPOSite.Title "-> It is potentially obsolete!"
$ObsoleteSPOGroups++
$ObsoleteReportLine = $ObsoleteReportLine + " No SPO activity detected in the last 90 days."
}
Else
{
#Write-Host $AuditRecs.Count "audit records found for " $SPOSite.Title "the last is dated" $AuditRecs.CreationDate[0]
}}
Else
{

The SharePoint document library URL is blank, so the document library was never created for this group

#Write-Host "SharePoint has never been used for the group" $Group.DisplayName
$ObsoleteSPOGroups++
$ObsoleteReportLine = $ObsoleteReportLine + " SPO document library never created."
}

Report to the screen what we found - but only if something was found...

If ($ObsoleteReportLine -ne $Group.DisplayName)
{
Write-Host $ObsoleteReportLine
}

Generate the number of warnings to decide how obsolete the group might be...

If ($AuditRecs -eq $Null)
{
$SPOActivity = "No SPO activity detected in the last 90 days"
$NumberWarnings++
}
If ($Group.SharePointDocumentsUrl -eq $Null)
{
$SPOStatus = "Document library never created"
$NumberWarnings++
}

$Status = "Pass"
If ($NumberWarnings -eq 1)
{
$Status = "Warning"
}
If ($NumberWarnings -gt 1)
{
$Status = "Fail"
}

If Team-Enabled, we can find the date of the last chat compliance record

If ($TeamsEnabled -eq $True)
{
$TeamChatData = (Get-MailboxFolderStatistics -Identity $Group.Alias -IncludeOldestAndNewestItems -FolderScope ConversationHistory)
If ($TeamChatData.ItemsInFolder[1] -ne 0) {
$LastItemAddedtoTeams = $TeamChatData.NewestItemReceivedDate[1]
$NumberofChats = $TeamChatData.ItemsInFolder[1]
}
}

$Members = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType members #| Select-Object -ExpandProperty "Name"
$Owners = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType owners #| Select-Object -ExpandProperty "Name"
$MembersCount = $Members.count
$OwnerCount = $Group.ManagedBy
$SharePointUrl = $Group.SharePointSiteUrl
$SharePointSite = Get-SPOSite -Identity $SharePointUrl
$SharePointLastModified = $SharePointSite.LastContentModifiedDate | Get-Date -Format yyyy.MM.dd
$TeamsEnabledCheck = Get-MailboxFolderStatistics -Identity $Group.Alias -IncludeOldestAndNewestItems -FolderScope ConversationHistory | Select-Object FolderType

If ($TeamsEnabledCheck.FolderType -like 'TeamChat') {

$TeamsEnabled = "True"
$TeamsCount++

}
else {
$TeamsEnabled = "False"
}

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
TeamsEnabled = $TeamsEnabled
LastChat = [string]$LastItemAddedtoTeams
NumberChats = [string]$NumberofChats
LastConversation = [string]$LastConversation
NumberConversationsInbox = $NumberConversations
SPOActivity = [string]$SPOActivity
SPOStatus = $SPOStatus
NumberWarnings = $NumberWarnings
Status = $Status
ExternalMemberCount = $Group.GroupExternalMemberCount
WhenChanged = [string]$Group.WhenChanged
SharePointLastModified = [string]$SharePointLastModified
Description = $Group.Notes
}#EndPSCustomObject
$GroupsInfo+=$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
TeamsEnabled = $TeamsEnabled
LastChat = [string]$LastItemAddedtoTeams
NumberChats = [string]$NumberofChats
LastConversation = [string]$LastConversation
NumberConversationsInbox = $NumberConversations
SPOActivity = [string]$SPOActivity
SPOStatus = $SPOStatus
NumberWarnings = $NumberWarnings
Status = $Status
ExternalMemberCount = $Group.GroupExternalMemberCount
WhenChanged = [string]$Group.WhenChanged
SharePointLastModified = [string]$SharePointLastModified
Description = $Group.Notes
}#EndPSCustomObject
$GroupsInfo+=$object
}

$object2=[PSCustomObject]@{

Name = $Group.DisplayName
NumberOfOwners = $OwnerCount.count
Date = Get-Date -format dd.MM.yyyy
}#EndPSCustomObject
$NumberofOwners+=$object2

$object3=[PSCustomObject]@{

Name = $Group.DisplayName
NumberOfMembers = $MembersCount
Date = Get-Date -format 'dd.MM.yyyy HH:MM'
}#EndPSCustomObject
$NumberofMembers+=$object3

$GroupsCount--

}

$TeamsCountArray = @()
$object4=[PSCustomObject]@{

NumberOfTeams = $TeamsCount
Date = Get-Date -format 'dd.MM.yyyy HH:MM'
}#EndPSCustomObject
$TeamsCountArray+=$object4

$object5=[PSCustomObject]@{

NumberOfGroups = $Groups.Count
Date = Get-Date -format 'dd.MM.yyyy HH:MM'
}#EndPSCustomObject
$NumberofGroups+=$object5

$col1 = New-PowerBIColumn -Name Name -DataType String
$col2 = New-PowerBIColumn -Name Group -DataType String
$col3 = New-PowerBIColumn -Name Email -DataType String
$col4 = New-PowerBIColumn -Name UserName -DataType String
$col5 = New-PowerBIColumn -Name NumberOfMembers -DataType Int64
$col6 = New-PowerBIColumn -Name MemberOrOwner -DataType String
$col7 = New-PowerBIColumn -Name NumberOfOwners -DataType Int64
$col8 = New-PowerBIColumn -Name GroupType -DataType String
$col9 = New-PowerBIColumn -Name TeamsEnabled -DataType String
$col10 = New-PowerBIColumn -Name LastChat -DataType String
$col11 = New-PowerBIColumn -Name NumberChats -DataType Int64
$col12 = New-PowerBIColumn -Name LastConversation -DataType String
$col13 = New-PowerBIColumn -Name NumberConversationsInbox -DataType Int64
$col14 = New-PowerBIColumn -Name SPOActivity -DataType String
$col15 = New-PowerBIColumn -Name SPOStatus -DataType String
$col16 = New-PowerBIColumn -Name NumberWarnings -DataType Int64
$col17 = New-PowerBIColumn -Name Status -DataType String
$col18 = New-PowerBIColumn -Name ExternalMemberCount -DataType Int64
$col19 = New-PowerBIColumn -Name WhenChanged -DataType String
$col20 = New-PowerBIColumn -Name SharePointLastModified -DataType String
$col21 = New-PowerBIColumn -Name Description -DataType String
$col22 = New-PowerBIColumn -Name NumberOfGroups -DataType Int64
$col23 = New-PowerBIColumn -Name Date -DataType DateTime
$col24 = New-PowerBIColumn -Name NumberOfTeams -DataType Int64
$col25 = New-PowerBIColumn -Name Date -DataType DateTime
$col26 = New-PowerBIColumn -Name Name -DataType String
$col27 = New-PowerBIColumn -Name NumberOfOwners -DataType Int64
$col28 = New-PowerBIColumn -Name Date -DataType DateTime
$col29 = New-PowerBIColumn -Name Name -DataType String
$col30 = New-PowerBIColumn -Name NumberOfMembers -DataType Int64
$col31 = New-PowerBIColumn -Name Date -DataType DateTime

#Creates table from defined collums.
#Comment out after first time setup.
$GroupsTable = New-PowerBITable -Name GroupsTable -Columns $col1,$col2,$col3,$col4,$col5,$col6,$col7,$col8,$col9,$col10,$col11,$col12,$col13,$col14,$col15,$col16,$col17,$col18,$col19,$col20,$col21
$NumberofGroupsHistoryTable = New-PowerBITable -Name NumberofGroupsHistoryTable -Columns $col22,$col23
$TeamsCountHistoryTable = New-PowerBITable -Name TeamsCountHistoryTable -Columns $col24,$col25
$NumberofOwnersHistoryTable = New-PowerBITable -Name NumberofOwnersHistoryTable -Columns $col26,$col27,$col28
$NumberofMembersHistoryTable = New-PowerBITable -Name NumberofMembersHistoryTable -Columns $col29,$col30,$col31

#Creates dataset from defined table.
#Comment out after first time setup.
$GroupsDataset = New-PowerBIDataSet -Name GroupsDataSet -Tables $GroupsTable,$NumberofGroupsHistoryTable,$TeamsCountHistoryTable,$NumberofOwnersHistoryTable,$NumberofMembersHistoryTable

#Adds dataset and get datasetid.
#Comment out after first time setup.
#$WorkSpace = Get-PowerBIWorkspace -Name "HuntonAK Reporting"
$GroupsDatasetID = Add-PowerBIDataSet -Dataset $GroupsDataset #-Workspace $WorkSpace

#Take note of datasetid so you have it for when you are updating the dataset.
#$GroupsDatasetID = '68bf9dde-73d8-4593-be37-854940e3b89d'
$GroupsDatasetID

#Remove commeting after firsttime setup on line below.
Remove-PowerBIRow -DataSetId $GroupsDatasetId.Id -TableName GroupsTable

Add-PowerBIRow -DatasetId $GroupsDatasetId.Id -TableName GroupsTable -Rows $GroupsInfo
Add-PowerBIRow -DataSetId $GroupsDatasetId.Id -TableName NumberofGroupsHistoryTable -Rows $NumberofGroups
Add-PowerBIRow -DataSetId $GroupsDatasetId.Id -TableName TeamsCountHistoryTable -Rows $TeamsCountArray
Add-PowerBIRow -DataSetId $GroupsDatasetId.Id -TableName NumberofOwnersHistoryTable -Rows $NumberofOwners
Add-PowerBIRow -DataSetId $GroupsDatasetId.Id -TableName NumberofMembersHistoryTable -Rows $NumberofMembers
`

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the supplied PowerShell code around the final Add-PowerBIRow calls and the New-PowerBITable definitions. Compare the row objects with the dataset and table schemas, then reproduce the BadRequest using the smallest failing input. Done means identifying the rejected field or request shape and documenting a verified correction.

Written by the indexing model from the issue text.

Assessment

Tech stack
powershell
Domain
analytics, cli
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.