Forum Discussion

SEGA's avatar
SEGA
New Member
2 years ago

Add identifier to first duplicate value with multiple conditions

Hi everyone 🙂

 

Im struguling trying to achieve the following:
I have the following table, I want to mark with a 1 the ones thaat are unique and the first duplicate registry, the other repeated duplicates leave them with 0, I want to consider a duplicate only when the PR + Spend + PR Status (is everything except "Denied") + Approver Status (Is "Approved") is duplicate. 

 

The desired result would be the last column named Unique. I dont have the Count column in my power query table,.

 

Some other values for PR Status are "Confirmed", "Received" and for Approve Status only "Denied" and "Approved"

 

I would really apreciate your help!

 

PRSpendCountPR StatusApprover StatusUnique
PR30168672710.871RecibidoApproved1
PR3362502483.791RecibidoApproved1
PR3364571398.252RecibidoApproved1
PR3364571398.252RecibidoApproved0
PR33716828843.571RecibidoApproved1
PR35241205367.651PedidoApproved1
PR3524207600.822RecibidoApproved1
PR3524207600.822RecibidoApproved0
PR3527348-V217122.461RecepciónApproved1
Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZC9DcIwFIR3cR0s+z2/n5RsEKWgidIQu3BDLAoWYCQ2gMWwoaJAERLdSffd6XTTZIYRnWdlMZ0B8c5qU2Na8jHHtcp9Kef1kqKZuxeNDOSgGkHRSr8FBxJfDezVAv0Tlrq6zVANaGljNEHw4KpByGK5dQ8pfmXBtT529Q3YLP4FFgy6OzTMiwewgd+JVJb8uN5vp4/U/AQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PR = _t, Spend = _t, #"PR Status" = _t, #"Approver Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PR", type text}, {"Spend", type number}, {"PR Status", type text}, {"Approver Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PR", "Spend", "PR Status", "Approver Status"}, {{"Rows", each _, type table [PR=nullable text, Spend=nullable number, PR Status=nullable text, Approver Status=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows], "Index", 1, 1, Int64.Type)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"PR", "Spend", "PR Status", "Approver Status", "Index"})
in
    #"Removed Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".