Forum Discussion

FlightofICARUS's avatar
FlightofICARUS
Frequent Visitor
4 years ago
Solved

Help with duplicate data entries where ID has more than one Status

Hi, Hope someone can guide me in the right direction.

 

I have a dataset that contains job dispatch data entries.
When creating a job you have either quote/work order/completed/unsuccessful phases.

 

I need to remove duplicates where a job with the same Purchase Order number has for example, three different entries on three different job ids where two are closed on 'unsuccessful' status and one on 'completed'.

In this scenario I need to filter out the two unsuccessful jobs as the completed job takes precedence.

In return if there are two duplicate entries on different job IDs where both are Unsuccessful and have the same Purchase Order number I need to return the first created entry.

 

 

Is this possible in Power Query or do I need to look @ a DAX measure?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    llll

     

     

     

    give it a try and tell me what needs to be fixed, if at least it's close to what you're looking for

     

    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjYwDHLz8VbSUTIGYuf83IKc1JLUFKVYHXS50Lzi0uTk1OLitNIcYqU9XE1wmoyQI6AVq3RQiClVpDHcFWLsakSEHDZjCUkHuDnhNBkhR0ArNulQJz9XqkhjuCvUNTQKn1aKpCMNIsyAUia0lkb3lZFhuHcYbnchS+PTGp5flK3gX5SSWqQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [pon = _t, Po = _t, status = _t]),
    
#"Raggruppate righe" = Table.Group(Origine, {"pon"}, {{"all", each _}, {"distinct", each Table.RowCount(Table.Distinct(_, {"status"})), Int64.Type}}),
tac=Table.AddColumn(#"Raggruppate righe", "newcol",each   if  [distinct] < Table.RowCount([all]) then 
                   (if List.ContainsAll([all][status],{"Completed", "Unsuccessful"}) then "Completed "  else  
                                                                if List.Distinct([all][status])={"Unsuccessful"} then  [all]{0} else "" )
                                                                else [pon])
in
    tac

     

     

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I think it is possible in PQ.

    But you have to give us two table (that can be easily copied): the starting one and the wanted one and explain the rules to apply.

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      4 years ago

      You should be able to do it using the Table.Group function with a custom aggregation.  In your question, you mention analyzing by PO number and also by Job ID.  But in your table, there is no column titled "Job ID"

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you for your reply,

        When I used the group by function it did a count on all Purchase Order Numbers.

        Here is screenshot of a step back

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, that would be amazing,

     

    I hope this format is what you were looking for.

     

    Output on duplicates should be

    IF DISTINCT(Purchase Order Number) = duplicate & Status = Completed & Status = Unsuccessful then Completed.

    IF DISTINCT(Purchase Order Number) = Duplicate & Status = unsuccessful then 1st entry of Unsuccessful

    else DISTINCT(Purchase order number)

    purchase_order_number PO_Num_Count SM8_FAM.status
    "C01RFLK" 3 "Completed"
    "C01RFLK" 3 "Unsuccessful"
    "C01RFLK" 3 "Unsuccessful"
    "C01RHE4" 3 "Completed"
    "C01RHE4" 3 "Unsuccessful"
    "C01RHE4" 3 "Unsuccessful"
    "C01RRT5" 3 "Unsuccessful"
    "C01RRT5" 3 "Unsuccessful"
    "C01RRT5" 3 "Completed"
    "C01T3E2" 3 "Completed"
    "C01T3E2" 3 "Unsuccessful"
    "C01T3E2" 3 "Unsuccessful"
    "C01TPFB" 3 "Completed"
    "C01TPFB" 3 "Unsuccessful"
    "C01TPFB" 3 "Unsuccessful"
    "C01UBNE" 3 "Unsuccessful"
    "C01UBNE" 3 "Unsuccessful"
    "C01UBNE" 3 "Completed"
    "C01UEUZ" 3 "Unsuccessful"
    "C01UEUZ" 3 "Unsuccessful"
    "C01UEUZ" 3 "Unsuccessful"
    "C01Y0X6" 4 "Unsuccessful"
    "C01Y0X6" 4 "Unsuccessful"
    "C01Y0X6" 4 "Unsuccessful"
    "C01Y0X6" 4 "Completed"
    "C021WKV" 3 "Unsuccessful"
    "C021WKV" 3 "Completed"
    "C021WKV" 3 "Work Order"

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      llll

       

       

       

      give it a try and tell me what needs to be fixed, if at least it's close to what you're looking for

       

      let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjYwDHLz8VbSUTIGYuf83IKc1JLUFKVYHXS50Lzi0uTk1OLitNIcYqU9XE1wmoyQI6AVq3RQiClVpDHcFWLsakSEHDZjCUkHuDnhNBkhR0ArNulQJz9XqkhjuCvUNTQKn1aKpCMNIsyAUia0lkb3lZFhuHcYbnchS+PTGp5flK3gX5SSWqQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [pon = _t, Po = _t, status = _t]),
    
#"Raggruppate righe" = Table.Group(Origine, {"pon"}, {{"all", each _}, {"distinct", each Table.RowCount(Table.Distinct(_, {"status"})), Int64.Type}}),
tac=Table.AddColumn(#"Raggruppate righe", "newcol",each   if  [distinct] < Table.RowCount([all]) then 
                   (if List.ContainsAll([all][status],{"Completed", "Unsuccessful"}) then "Completed "  else  
                                                                if List.Distinct([all][status])={"Unsuccessful"} then  [all]{0} else "" )
                                                                else [pon])
in
    tac

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi, apologies for the late response. This solution definitely guided me in the right direction. Thank you so much!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi FlightofICARUS ,

     

    Any updates?

    Could you tell me if your problem has been solved by Anonymous 's method? If it is, kindly Accept it as the solution. More people will benefit from it.

    Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Best Regards,
    Eyelyn Qin