How to expand object values in powershell array

Question

I have written a powershell script:

$getAllSubIds = Get-AzSubscription
$test = $getAllSubIds[0]

$sqlMetaData = @()
foreach ($subscription in $getAllSubIds) {
    $info = "" | Select VmName, ResourceGroupName, SubscriptionID, SubscriptionName
    $setSubContext = Set-AzContext -Subscription $subscription
    $getSubVM = Get-AzSqlVM -DefaultProfile $setSubContext

    $info.VmName = $getSubVM.Name
    $info.ResourceGroupName =  $getSubVM.ResourceGroupName
    $info.SubscriptionID = $setSubContext.Subscription.Id
    $info.SubscriptionName = $setSubContext.Subscription.Name

    $sqlMetaData += $info
    }

$sqlMetaData looks like this:

enter image description here

user_45375 · Answer

Format-Table -Wrap is the key to allowing a single column value to span multiple lines.

For that, however, the values must be multi-line strings, not arrays, as in your case, so you need a calculated property in order to transform arrays to multi-line strings.

A simplified example:

Default formatting represents arrays enclosed in { ... }, always on a single line, with elements that don't fit truncated with ...:

PS> [pscustomobject] @{ foo = , "x" * 5; bar = 42 } 

foo           bar
---           ---
{x, x, x, x…}  42

Using Format-Table with a calculated property that turns the 5-element array into a 5-line string via join "`n", which is then shown in full, thanks to -Wrap:

PS> [pscustomobject] @{ foo = , "x" * 5; bar = 42 } | 
      Format-Table -Wrap @{ n='foo'; e={ $_.foo -join "`n" } }, bar

foo       bar
---       ---
x          42
x
x
x
x

^{Note: You must use a calculated property (@{ ... }) for each property that you want represented this way.}

In order to show duplicates of the non-array property values alongside the expanded array elements, use the following:

PS> [pscustomobject] @{ foo = , "x" * 5; bar = 42 } | 
      Format-Table -Wrap @{ n='foo'; e={ $_.foo -join "`n" } }, 
                         @{ n='bar'; e={ , $_.bar * $_.foo.Count -join "`n" } }

foo       bar
---       ---
x         42
x         42
x         42
x         42
x         42

Caveats:

Format-* cmdlets should only ever be used to produce for-display output; they output formatting instructions rather than the original data.
- In order to make PowerShell default to the custom table formatting above (which then wouldn't interfere with later processing as data), you'd have to author formatting data and associate it with the .NET type(s) of interest.
Large arrays among the property values can make the output unreadable; you could implement custom truncation, however (e.g., show at most 5 lines).

user_11954025 · Answer

You should be able to do something like this as long as the VmNames and ResourceGroupNames are all strings

$sqlMetaData | 
    Select-Object @{name = 'VmNames'; expression = { $_.VmName -join ', ' } },
                  @{name = 'ResourceGroupNames'; expression = { $_.ResourceGroupName -join ', ' } },
                  SubscriptionID,
                  SubscriptionName

Or

$sqlMetaData | 
    Select-Object @{name = 'VmNames'; expression = { $_.VmName -join "`n" } },
                  @{name = 'ResourceGroupNames'; expression = { $_.ResourceGroupName -join "`n" } },
                  SubscriptionID,
                  SubscriptionName | 
                      Format-Table -Wrap

How to expand object values in powershell array

2 Answers