Forum Discussion
Helper IIIAdd 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 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.
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)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.
5 Replies
camargos88Community Champion
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
lotus22Thank 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
error received
Expression.Error: The field 'Type2' of the record wasn't found.
- lotus22
Type2 is a calculated column