Results 1 to 2 of 2

Thread: Sumproduct Query Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Sumproduct Query Excel

    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:
    A B C D E F
    Work Order1 Op 1 Part Number1
    Work Order1 Op 2 Part Number1
    Work Order1 Op 3 Part Number1
    Work Order2 Op 1 Part Number2
    Work Order2 Op 2 Part Number2
    Work Order3 Op 1 Part Number1
    Work Order3 Op 2 Part Number1

    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 by Excel_Blonde; Oct 4th, 2019 at 07:28 AM.

  2. #2
    New Member
    Join Date
    Aug 2018
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct Query Excel

    Quote Originally Posted by Excel_Blonde View Post
    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:
    A B C D E F
    Work Order1 Op 1 Part Number1
    Work Order1 Op 2 Part Number1
    Work Order1 Op 3 Part Number1
    Work Order2 Op 1 Part Number2
    Work Order2 Op 2 Part Number2
    Work Order3 Op 1 Part Number1
    Work Order3 Op 2 Part Number1

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •