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

  • lotus22's avatar
    lotus22
    Helper III
    6 years ago

    error received

     

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

    • lotus22's avatar
      lotus22
      Helper III
      6 years ago

      Type2 is a calculated column

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        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.