Sumproduct Query Excel

[Excel_Blonde]

Hi,

I'm trying to replicate a sumproduct formula in Power query editor within a custom column.

Previously used working formula:
=IF(SUMPRODUCT(($C$2:$C2=C2)*($E$2:$E2=E2))>1,0,1)

I know sumproduct isn't used in power query, and after searching the net have attempted to use SumX but this doesn't seem to be recognised either.

Currently I have :
if [#"W/Order"]*[Part Number]=[Part Number]>1 then 0 else 1


My data example, F is the custom column:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order1[/TD]
[TD]Op 1[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order1[/TD]
[TD]Op 2[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order1[/TD]
[TD]Op 3[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order2[/TD]
[TD]Op 1[/TD]
[TD]Part Number2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order2[/TD]
[TD]Op 2[/TD]
[TD]Part Number2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order3[/TD]
[TD]Op 1[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order3[/TD]
[TD]Op 2[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help or suggest a better way to achieve my goal.

The goal is to generate a pivot table where I can filter by a part number with an occurrence of more than 3.

Thanks in advance.

 

[Excel_Blonde]

Hi,

I'm trying to replicate a sumproduct formula in Power query editor within a custom column.

Previously used working formula:
=IF(SUMPRODUCT(($C$2:$C2=C2)*($E$2:$E2=E2))>1,0,1)

I know sumproduct isn't used in power query, and after searching the net have attempted to use SumX but this doesn't seem to be recognised either.

Currently I have :
if [#"W/Order"]*[Part Number]=[Part Number]>1 then 0 else 1


My data example, F is the custom column:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order1[/TD]
[TD]Op 1[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order1[/TD]
[TD]Op 2[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order1[/TD]
[TD]Op 3[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order2[/TD]
[TD]Op 1[/TD]
[TD]Part Number2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order2[/TD]
[TD]Op 2[/TD]
[TD]Part Number2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order3[/TD]
[TD]Op 1[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Work Order3[/TD]
[TD]Op 2[/TD]
[TD]Part Number1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help or suggest a better way to achieve my goal.

The goal is to generate a pivot table where I can filter by a part number with an occurrence of more than 3.

Thanks in advance.

I found the below on another site but couldn't get it to work. After further searching I thought it was due to the ',' so I changed it to then and else but still not working. It still displays Token RightParen expected. Any ideas?

=if (
CALCULATE (
COUNTROWS ( Test1 ),
FILTER (
ALLEXCEPT ( Test1, Test1[Works Order Number] ),
Test1[Index] <= EARLIER ( Test1[Index] )
)
)
> 1,
0,
1
)


Current:
=if (
CALCULATE (
COUNTROWS ( Test1 ),
FILTER (
ALLEXCEPT ( Test1, Test1[Works Order Number] ),
Test1[Index] <= EARLIER ( Test1[Index] )
)
)
> 1 then
0 else
1
)

Any help greatly appreciated.