PowerShell - Loop Through Nested Objects

Question

I'm writing a PowerShell script to loop through an organization's PowerBI workspaces and spit out the report IDs, names, and URLs for each workspace - so, a nested loop.

Here's my working script thus far:

Install-Module -Name MicrosoftPowerBIMgmt
$myArray = @()
Login-PowerBIServiceAccount
Get-PowerBIWorkspace -Scope Organization | ForEach-Object { 
 $ws = $_
 Get-PowerBIReport -WorkspaceId $_.Id | ForEach-Object {
 $x = [pscustomobject]@{
 workspaceId = $ws.Id
 workspaceName = $ws.Name
 reportId = $_.id
 reportName = $_.Name
 reportUrl = $_.WebUrl
 }
 $myArray += $x
 }
}
$myArray | Export-Csv "PowerBIWorkspaceReports.csv"

Four questions:

1) Is there a better way to add the module dependency at the top? I can't find any info on how PowerBI looks for existing module dependencies.

2) Are the ForEach-Object calls redundant? I see in simple code people assume whatever command runs after the pipe runs just fine whether on a single object or a collection of objects.

3) Is there a better way to manage my objects? Since I need data from the first loop ( the Workspace name and ID) and the second loop, is there an easier way to join them (or export them as a flattened list) than looping?

4) Should I just append my rows directly to the CSV? There doesn't seem to be the equivalent of While(File.Open() ...) in PowerShell.

And any other advice appreciated.

• JosefZ: An other advice (performance related): roll down to Appending to arrays in the Slow Code: Top 5 Ways to Make Your PowerShell Scripts Run Faster article. Use $myArray = Get-PowerBIWorkspace ... omitting both $myArray = @() and $myArray += (and note that you could omit the auxiliary variable $x at all).

Answer 1

I rejigged your code a little to make it tidier. I don't have PowerBI, so I haven't tested it, and I can't guarantee that I haven't done anything dumb.

I got rid of the nested loop. Note the -PipelineVariable ws. What that does is put the output of that stage of the pipeline into a variable called $ws, which we can refer to later. It means that we don't need to do $ws = $_.

-PipelineVariable is one of the "Common Parameters", and is available on many or most cmdlets. You can abbreviate it to -PV, by the way.

I also got rid of the intermediate array $myArray since it wasn't needed.

Install-Module -Name MicrosoftPowerBIMgmt
Login-PowerBIServiceAccount
Get-PowerBIWorkspace -Scope Organization -PipelineVariable ws | 
 ForEach-Object { Get-PowerBIReport -WorkspaceId $_.Id } | 
 ForEach-Object {
 [pscustomobject]@{
 workspaceId = $ws.Id
 workspaceName = $ws.Name
 reportId = $_.id
 reportName = $_.Name
 reportUrl = $_.WebUrl
 }
 } |
 Export-Csv "PowerBIWorkspaceReports.csv"

• Kyle Hale: Thanks, this is exactly what I was looking for - is there a good tutorial for this sort of PowerShell piping object collections best practices? My Google-fu was a little scattershot.
• Dangph: @KyleHale there aren't any object collections as such here. It's just single objects moving around.
• Kyle Hale: Hey @Dangph thanks for the comment, I guess what I mean is, Get-PowerBIWorkspace returns multiple workspace objects. We pipe that to $ws. So how does that $ws also represent the iterated workspace (from ForEach-Object) when we're assigning values from $ws to the custom object? I understand what it's doing, it's just usually in code you have to do that explicitly, so I was wondering what internal mechanim PowerShell has to do that implicitly and if I can learn more about it.
• Dangph: @KyleHale, you can picture the pipeline as a series of tubes with ping-pong balls (or objects) going through them one at a time. Get-PowerBIWorkspace produces ping-pong balls and they go one at a time to the next stage, namely the ForEach-Object { Get-PowerBIReport ...}, which will take each ping-pong ball and transform it into a bunch of other objects, golf balls, let's say, which in turn go onto the next stage one at a time.
• Dangph: [cont.] The PipelineVariable just allows downstream stages to look upstream to see what ping-pong ball is currently being processed. Most of the time you won't need PipelineVariable, but every so often it will be helpful.