PowerShell script that automatically uploads PowerBI report to list of Workspaces

Question

I am trying to generate a script that automatically uploads a Power BI report to a list of workspaces, but since I'm not that experienced with PowerShell I feel a bit out of my depth.

Based on this post

https://dev.to/merill/powershell-script-to-generate-a-report-on-all-power-bi-workspaces-and-groups-in-your-microsoft-365-tenant-44pc

I have written a script that generates an array of workspaces:

Connect-PowerBIServiceAccount

$workspaces = Get-PowerBIWorkspace -Scope Organization -Include All

$wslist = @()
foreach ($ws in $workspaces) {
    $item = [ordered] @{
        Id = $ws.ID
        Name = $ws.Name
    }
    $u = new-object PSObject -Property $item
    $wslist += $u
}

Similarly, I have written a script that generates an array of report ID's uploaded to a given Workspace:

Connect-PowerBIServiceAccount

$workspaceid = "enter-id-here"

$reports = Get-PowerBIReport -WorkspaceId $workspaceid

$rlist = @()
foreach ($r in $reports) {
    $r
    $item = [ordered] @{
        Id = $r.ID
        Name = $r.Name
    }
    $u = new-object PSObject -Property $item
    $rlist += $u
}

What I want to do is merge these arrays into one that only includes the rows where the report name is equal to some predefined name stored in a string var.

Finally, I want to iterate through the array, and for each row delete the old report and then upload a new version.

Is your question just about building that list, or do you need help with uploading too? Have you had a look at Where-Object? — marsze
– marsze, Commented Oct 14, 2020 at 9:32
I have managed to upload a report via PS command, that seems to work well. Myissue is creating and iterating through the list of workspace and report ID's. I will take a look at Where-Object. — Mike
– Mike, Commented Oct 14, 2020 at 11:12

Andrey Nikolov · Accepted Answer · 2020-10-14 11:03:16Z

1

If I got your point correctly, you need something like this:

Import-Module MicrosoftPowerBIMgmt

$oldReportName = "Fancy Report"
$pbixFilePath = "C:\Power BI\Fancy Report.pbix"

$username = "[email protected]"
$password = "P@ssw0rd" | ConvertTo-SecureString -asPlainText -Force

$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential | Out-Null

# -All - Returns all results, not only top 100
$workspaces = Get-PowerBIWorkspace -All -Scope Organization -Include All

foreach($workspace in $workspaces) {

  $report = Get-PowerBIReport -WorkspaceId $workspace.Id -Scope Organization -Name $oldReportName

  if ($report) {
    Write-Host "Report $oldReportName found in workspace $($workspace.Name)..."
    New-PowerBIReport -Path $pbixFilePath -Workspace $workspace -ConflictAction CreateOrOverwrite
  }

}

Disconnect-PowerBIServiceAccount

$oldReportName is the name of the report, that you are looking for. $pbixFilePath is where the new version that should be uploaded is. I've added -All, because otherwise Get-PowerBIWorkspace will return only the first 100 workspaces in the tenant. And because this is tenant-wise operation, $username and $password should be admin credentials (as of October 2020 service principal cannot be used for admin operations).

There is no need to construct an array. Just iterate through the workspaces and check is there a report with this name published there ($report = Get-PowerBIReport -WorkspaceId $workspace.Id -Scope Organization -Name $oldReportName). If such one exists, upload the new one there and replace it (New-PowerBIReport -Path $pbixFilePath -Workspace $workspace -ConflictAction CreateOrOverwrite).

edited Oct 14, 2020 at 11:03

answered Oct 14, 2020 at 10:57

Andrey Nikolov

13.5k3 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mike Over a year ago

This is very close to what I am looking for. Thank you for pointing out that an array is not needed, this heavily simplifies things. Basically, all I need to do is generate a custom $pbixFilePath value for each report derived from workspace and report name, ie $pbixFilePath = "C:/~/Reports/WorkspaceId/ReportName.pbix"

Andrey Nikolov Over a year ago

If you move the assignment of $pbixFilePath inside the loop, you can use the workspace name, e.g. $pbixFilePath = "C:\Power BI\$($workspace.Name)\Fancy Report.pbix" (or use the workspace Id -> $pbixFilePath = "C:\Power BI\$($workspace.Id)\Fancy Report.pbix"). Is this what you were looking for?

Mike Over a year ago

Yes, this is what I was looking for. Strangely, after running the script successfully a few times with a dummy report, it becomes unresponsive and just hangs there. Is this normal behaviour? Get-PowerBIReport : A task was canceled. At line:18 char:13 + $report = Get-PowerBIReport -WorkspaceId $workspace.Id -Scope Organ ... + CategoryInfo : WriteError: (Microsoft.Power...etPowerBIReport:GetPowerBIReport) [Get-PowerBIReport], TaskCanceledException + FullyQualifiedErrorId : A task was canceled.,Microsoft.PowerBI.Commands.Reports.GetPowerBIReport

Andrey Nikolov Over a year ago

It could be due to different reasons, e.g. the request is rejected by the service (due to overloading or other reasons). I do not know do you have a dedicated capacities in your tenant, or all workspaces are using shared resources. If you will do this at scale, you may have to throttle the requests a bit.

Mike Over a year ago

Okay, I will wait and try again. Thank you very much for helping me out on this one! I will mark your answer as the solution.

Mike · Accepted Answer · 2020-10-15 05:15:41Z

The final, fully working PS script (with dummy names and paths) looks like this:

Connect-PowerBIServiceAccount

Import-Module MicrosoftPowerBIMgmt

# Report name here:
$ReportName = "AwesomeReport"

$FolderPath = "C:\Users\AwesomeUser\AwesomeReports"

$workspaces = Get-PowerBIWorkspace -All -Scope Organization -Include All

foreach($workspace in $workspaces) {

  $report = Get-PowerBIReport -WorkspaceId $workspace.Id -Scope Organization -Name $ReportName

  if ($report) {
   
    Write-Host "Report $ReportName found in workspace $($workspace.Name)..."
    
    $filePath = "$FolderPath\$($workspace.Name)\$ReportName.pbix"

    New-PowerBIReport -Path $filePath -WorkspaceId $workspace.Id -Name $ReportName -ConflictAction CreateOrOverwrite
  }

}

Disconnect-PowerBIServiceAccount

Collectives™ on Stack Overflow

PowerShell script that automatically uploads PowerBI report to list of Workspaces

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related