Sumproduct Query Excel

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44
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:
ABCDEF
Work Order1Op 1Part Number1
Work Order1Op 2Part Number1
Work Order1Op 3Part Number1
Work Order2Op 1Part Number2
Work Order2Op 2Part Number2
Work Order3Op 1Part Number1
Work Order3Op 2Part Number1

<tbody>
</tbody>

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.
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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:
ABCDEF
Work Order1Op 1Part Number1
Work Order1Op 2Part Number1
Work Order1Op 3Part Number1
Work Order2Op 1Part Number2
Work Order2Op 2Part Number2
Work Order3Op 1Part Number1
Work Order3Op 2Part Number1

<tbody>
</tbody>

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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top