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