Add custom column with Lookup on the same table

[lotus22]

Customer Part	Supplier Part	Line Type	Totals	Expected
CPart A		Customer Demand	12345	Empty
CPart A	SPartY	Supplier Order	56	12345
CPart D	SPartX	Supplier Order	56	Empty
CPart B		Customer Demand	5869	Empty
CPart E	SPartN	Supplier Order	25	Empty
CPart B	SPartO	Supplier Order	66	5689

In Expected column, I would like to bring Totals from Customer Demand when the Customer Part Number matches  Customer Part No in the Supplier Order field.

Therefore, the lookup will be only based on Customer Part Number but the data will only  come from the Customer Demand row. it is more like of multi filter.

[camargos88]

Hi @lotus22 ,

You can create a custom column like this:

if [Line Type] = "Supplier Order" then
let 
_customerPart = [Customer Part], 
_rows =  Table.SelectRows(#"Changed Type", each [Customer Part] = _customerPart and [Line Type] = "Customer Demand") 
in
if Table.IsEmpty(_rows) then null 
else _rows[Totals]{0}

else null

[lotus22]

error received

Expression.Error: The field 'Type2' of the record wasn't found.

[lotus22]

Type2 is a calculated column

[v-easonf-msft · Accepted solution]

Hi， @lotus22 

"Type 2"  is a calculated column ,so it cannot be found in Power Query.

You can try create a calculated column as below:

Column = 
var a=CALCULATE(SUM('Table'[Totals]),ALLEXCEPT('Table','Table'[Customer Part]),'Table'[Type]="Customer Demand")
return  IF('Table'[Type]="Customer Demand",BLANK(),a)

 pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lotus22]

Thank you, but i see all "null" when I incorporate same formula. I have about 30 columns in my power query

if [Type2] = "Supplier Order" then
let
_customerPart = [CustomerPart],
_rows = Table.SelectRows(#"Changed Type", each [CustomerPart] = _customerPart and [Type2] = "Customer Demand")
in
if Table.IsEmpty(_rows) then null
else _rows[Totals]{0}

else null