Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SEGA
New Member

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
1 REPLY 1
lbendlin
Super User
Super User

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".

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors