CODEWITHSUNDEEP
powershell
SherlockSpreadsheets
SherlockSpreadsheets

Reputation: 2360

Calculated field from variable value

Can anyone tell me what I am doing wrong in this PowerShell SELECT statement? I am trying to add a calculated property and assign a value from a variable. As seen in the debugger screencapture below, there the property values are displayed. But when added to the property the values are blank.

foreach ($ws in $Groups) {
    # Variables
    $wsName = $ws.Name.ToString()
    $wsId = $ws.Id.ToString()
    $Fileout = "C:\OneDrive\BI\OneDrive - Circle 9\PBI\Reports\$wsName\_EXPORT"
    # Workspaces
    $wsReports = Get-PowerBIReport -WorkspaceId $ws.Id
    $wsReports | SELECT *, @{Name = 'Workspace'; Expression = $wsName} , @{Name = 'WorkspaceId'; Expression = $wsId} 

    Write-Host($ws.Name + "...")
    Write-Host(($wsReports.count).ToString() + " report count") -ForegroundColor Cyan -BackgroundColor DarkGreen

    $wsReportsAll += $wsReports
 #   $wsReports | ft -auto
}

enter image description here

Upvotes: 0

Views: 209

Answers (1)

zett42
zett42

Reputation: 27756

When you want the value of a variable to be used for a calculated property, then you have to enclose it in curly braces:

$wsReports | SELECT *, @{Name = 'Workspace'; Expression = { $wsName }} , @{Name = 'WorkspaceId'; Expression = { $wsId }}

This assigns actual script blocks as the expressions. The script blocks return the value of the variables, by using PowerShells implicit output behaviour.

The syntax you were using (that is, assigning a string to Expression) has a different meaning. It would look for a property named like the value of the variable in $wsReports. E. g. Expression = $wsName would work like Select -Property 'Accounting BI Reports'. This can be useful for renaming properties, but is not what you actually want.

Upvotes: 1

Related Questions