Using variable in Select-Object in Powershell

Question

I have a table which is created using Time as the Column names. However if the first row does not have data in certain Hours(column) then the column does not show the column for the rest of the table. Thus, I'm trying to create a dynamic select statement which will get every hour since midnight and use these hours as search variables. I have accomplish to get the Select statement created but its not invoking/executing the query.

Any suggestion to get data returned and build the HTML table would be great appreciated.

Code:

$UniversalCurrTime = ((Get-Date).ToUniversalTime()).ToString('h tt')
$UniversalMidnight = ((Get-Date).ToUniversalTime().Date).ToString('h tt')
$hoursBetween = $UniversalMidnight

Write-host 'This is midnight: ' $UniversalMidnight
Write-host 'This is Current time: ' $UniversalTime
$HourSpan = @()
$HourSpan += 'VaultName'
$HourSpan += $UniversalMidnight #($hoursBetween).ToString('h tt')
Do{
    $HourSpan += "'" + ($hoursBetween).ToString('h tt') + "'"
    $hoursBetween = (Get-date $hoursBetween).AddHours(1)
    Write-host 'This is time being added to collection: ' $hoursBetween
}while($hoursBetween -le $UniversalCurrTime)

$HourSpan += 'TotalMessages'

$selectObject = '$reportList |Select-Object ' + (($HourSpan -split '\n') -join ",") + '| ConvertTo-Html -Fragment -ErrorAction SilentlyContinue'
$html = Invoke-Command -ScriptBlock {$selectObject}

Output on the screen but not executed to return data.

$reportList |Select VaultName,'12 AM','1 AM','2 AM','3 AM','4 AM','5 AM','6 AM','7 AM','8 AM', '9 AM','10 AM','11 AM','12 PM','1 PM','2 PM','3 PM','4 PM','5 PM','6 PM','7 PM', '8 PM', '9PM', '10 PM', '11 PM', 'TotalMessages' |Where-Object { -Not [String]::IsNullOrWhiteSpace($_) }| ConvertTo-Html -Fragment -ErrorAction SilentlyContinue

This statement should be executed.

Answer 1

$selectObject = '$reportList |Select-Object ' + (($HourSpan -split '\n') -join ",") + '| ConvertTo-Html -Fragment -ErrorAction SilentlyContinue'

The above creates a string value.

$html = Invoke-Command -ScriptBlock {$selectObject}

The above - which could be simplified to $html = & { $selectObject } and ultimately $html = $selectObject - outputs the string stored in $selectObject, which explains what you saw.

---

Leaving aside whether the array stored in $HourSpan is (a) constructed efficiently,^[1] (b) whether the intended chronological comparisons work and (c) assuming that $HourSpan += 'VaultName' and $HourSpan += 'TotalMessages' are removed, you're probably looking for the following:

$html = 
  $reportList |
  Select-Object (@('VaultName') + $HourSpan + 'TotalMessages') |
  ConvertTo-Html -Fragment -ErrorAction SilentlyContinue

That is, you can pass a dynamically constructed array of property names directly to the (positionally implied) -Property parameter of Select-Object.

With an array as the LHS - which use of @(...), the array-subexpression operator ensures - the + operator performs (flat) array concatenation.

---

PowerShell rarely requires constructing commands dynamically as strings and doing so should generally be avoided, due to inherent security risks, but in cases where this is necessary, you have two options:

• For one-off calls in the caller's scope, use Invoke-Expression; e.g.:

  $commandString = 'Get-Date'
  Invoke-Expression $commandString
  

• For repeatable calls, construct a script block from the string, using [scriptblock]::Create(), which you can invoke repeatedly on demand, either via &, the call operator - in a child scope - or via . , the dot-sourcing operator - directly in the caller's scope:

  $commandString = 'Get-Date'
  $scriptBlock = [scriptblock]::Create($commandString)
  # Invoke the script block on demand, in a child scope.
  # To invoke it directly in the caller's scope, use:
  #  . $scriptBlock
  & $scriptBlock
  

---

^{[1] Extending arrays in a loop with += is inefficient, because a new array must be created behind the scenes in every iteration, given that arrays are of fixed size; a much more efficient approach is to use a statement such as a do { ... } while (...) or foreach loop as an expression and let PowerShell itself collect the outputs in an array: [array] $outputs = foreach (...) { ... } - see this answer. In case you need to create arrays manually, e.g. to create multiple ones, use an efficiently extensible list type - see here.}