Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
PR | Spend | Count | PR Status | Approver Status | Unique |
PR3016867 | 2710.87 | 1 | Recibido | Approved | 1 |
PR3362502 | 483.79 | 1 | Recibido | Approved | 1 |
PR3364571 | 398.25 | 2 | Recibido | Approved | 1 |
PR3364571 | 398.25 | 2 | Recibido | Approved | 0 |
PR3371682 | 8843.57 | 1 | Recibido | Approved | 1 |
PR3524120 | 5367.65 | 1 | Pedido | Approved | 1 |
PR3524207 | 600.82 | 2 | Recibido | Approved | 1 |
PR3524207 | 600.82 | 2 | Recibido | Approved | 0 |
PR3527348-V2 | 17122.46 | 1 | Recepción | Approved | 1 |
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".
Check out the July 2025 Power BI update to learn about new features.