Powershell - PSCustomObject with Calculated Property

I'm importing a .csv file which I'm then modifying using calculated properties in a PSCustomObject. I'm stuck on one calculation where I'm attempting to lookup a value from a datarow object using one of the .csv values. We receive data with the supplier Part No and I need to lookup our corresponding Part No. Would you be able to suggest how best to do this please?

The csv content looks like this:

Vendor Code,Part No,Part Description,Bonded,Quantity,PO No,Vendor Ref
TEZ,ABC1234,Dark Blue,No,50,4378923,ORD089234
TEZ,BBC1256,Orange,No,20,4378923,ORD089234
TEZ,ACD1349,Green,No,10,4378923,ORD089234

The SQL query $SKUs returns this as datarows:

ITEMNO VALUE
TYP-5063 ABC1234
TYP-5037 BBC1256
TYP-8069 ACD1349

So I'm looking to use the 'Part No' field from the .csv file to run a lookup against $SKUs.VALUE and return the matching $SKUs.ITEMNO.

The output .csv will then include a column called 'OUR_SKU' containing the $SKUs.ITEMNO value.

Here is my code so far:

$Files = Get-ChildItem -Path "D:\Imports\Test\INVENTORY_HUB_RECEIPTS"
$ProcessingPath = "D:\Imports\Test\INVENTORY_HUB_RECEIPTS\Processing\"
$UKEntity = "TESTTRG"
$HUB_ID = "TEST"
$SKUs = Invoke-Sqlcmd -ServerInstance "localhost" -Database "XXXX" -Query "SELECT RTRIM(ITEMNO) AS ITEMNO, RTRIM(VALUE) AS VALUE FROM [XXXX].[dbo].[ICITEMO] WHERE OPTFIELD = 'CUSTITMNO' AND VALUE <>''"

foreach ($file in $Files) {

    
    $Content = (Import-Csv -path ($ProcessingPath + $file.Name)) |
    Select-Object @{n='HUB_ID'; e={ $HUB_ID }},
    @{e={$_.'Part No'}; l='PART_NO'},
    @{e={$_.Quantity}; l='QTY_RECEIVED'},
    DATE,
    @{n='ENTITY'; e={ $UKEntity }},
    @{e={$_.'Vendor Ref'.Substring($_.'Vendor Ref'.Length -8)}; l='ORDER_ID'},

    @{n='OUR_SKU'; e={ $SKUs | Where-Object {$($_.VALUE) -eq '123ABC'} | Select-Object -ExpandProperty ITEMNO}},
    @{n='OUR_SKU_X'; e={ $SKUs | Where-Object {$($_.VALUE) -eq $_.'PART_NO'} | Select-Object -ExpandProperty ITEMNO}}

    if ($Content.Count -eq 0) {Remove-Item ($ProcessingPath + $file.Name)} else {$Content | Export-Csv -Path ($ProcessingPath + $file.Name) -Not -Force}
    }

I've tried two examples for the new property 'OUR_SKU' this works but is obviously a static value. The property 'OUR_SKU_X' is my attempt to use the supplied $.'PART_NO' and this currently returns a blank field. The variable $SKUs does contain data and so does $.'PART_NO'. I'm thinking it's either a simple syntax error or it's not possible to use $_.'PART_NO' in the script block?

Thanks

Colin

Per comments, inside the where-object scriptblock on the line:

$SKUs | Where-Object {$($_.VALUE) -eq $_.'PART_NO'}

the automatic variable $_ relates to the individual items piped in from $SKUs, which hides the outer $_ from the $Content = (Import-Csv ...) | Select-Object ...

If you want to be able to access the outer $_ inside the where-object you'll need to capture it into a temporary variable like this:

e={ $tmp = $_; $SKUs | Where-Object { $_.VALUE -eq $tmp.PARTNO } | Select-Object -ExpandProperty ITEMNO}}

Here's a cut-down example:

$parts = @"
Vendor Code,Part No,Part Description,Bonded,Quantity,PO No,Vendor Ref
TEZ,ABC1234,Dark Blue,No,50,4378923,ORD089234
TEZ,BBC1256,Orange,No,20,4378923,ORD089234
TEZ,ACD1349,Green,No,10,4378923,ORD089234
"@ | ConvertFrom-Csv

$skus = @"
ITEMNO,VALUE
TYP-5063,ABC1234
TYP-5037,BBC1256
TYP-8069,ACD1349
"@ | ConvertFrom-Csv

$results = $parts | select-object @(
    @{l="PART_NO";  e={ $_."Part No" } },
    @{l="DESC";     e={ $_."Part Description" } },
    @{n='OUR_SKU';  e={ $part = $_; $skus | where-object { $_.VALUE -eq $part."Part No" } | Select-Object -ExpandProperty ITEMNO} }
)

Note the $part = $_; and $_.VALUE -eq $part."Part No" inside the definition of the third calculated property.

The output from the above is:

$results

PART_NO DESC      OUR_SKU
------- ----      -------
ABC1234 Dark Blue TYP-5063
BBC1256 Orange    TYP-5037
ACD1349 Green     TYP-8069