1
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ 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). \$\endgroup\$ – JosefZ May 23 at 16:34
2
\$\begingroup\$

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"
\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Kyle Hale May 24 at 16:07
  • \$\begingroup\$ @KyleHale there aren't any object collections as such here. It's just single objects moving around. \$\endgroup\$ – Dangph May 25 at 7:06
  • \$\begingroup\$ 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. \$\endgroup\$ – Kyle Hale May 29 at 16:30
  • \$\begingroup\$ @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. \$\endgroup\$ – Dangph Jun 3 at 6:46
  • \$\begingroup\$ [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. \$\endgroup\$ – Dangph Jun 3 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.