Forum Discussion

lotus22's avatar
lotus22
Helper III
6 years ago
Solved

Add custom column with Lookup on the same table

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 O...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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.

     

camargos88's avatar
camargos88
Community Champion
6 years ago

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's avatar
lotus22
Helper III
6 years ago

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