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

[FlightofICARUS]

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?

[Anonymous]

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

[Anonymous]

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 · Accepted solution]

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]

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

[Anonymous]

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.

[FlightofICARUS]

I've grouped Rows as screenshot indicates

[ronrsnfld]

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]

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

[ronrsnfld]

Yes, you should definitely be able to use the Table.Group function with a custom aggregation.  If you need some help writing that, post your sample data as text that can be copy/pasted into a table.