Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi guys,
just a quick question, I got a table, some of the principal columns I'am using are User ID, Date, Grade and Status.
The table collects information about determined users compleating courses. Users need more than an 80% to pass and they can be repeated in the column due they can try doing the course more than once.
Tried out with the "delete duplicates" option in Query Editor, but sometimes it keeps line with Status NO OK and sometimes with OK.
So what I need is to delete repeated user rows, if user is repeated with OK and NO OK keep only those that have status OK.
User ID | Date | Grade | Status |
654321 | 8/8/17 | 10 | NO OK |
654321 | 8/8/17 | 20 | NO OK |
123456 | 8/8/17 | 24 | NO OK |
123456 | 8/8/17 | 100 | OK |
654321 | 8/8/17 | 80 | OK |
123456 | 8/8/17 | 30 | NO OK |
123456 | 8/8/17 | 43 | NO OK |
Thanks!!
Solved! Go to Solution.
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1MTYyVNJRstC30DcyMDQHMg0NgISfv4K/t1KsDnYlRqhKDI2MTUzN0JSYEFRiaAAyBo81Fkjy2Awwhjvj0AKcikyMEQ6JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Date = _t, Grade = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Date", type date}, {"Grade", Int64.Type}, {"Status", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Status", Order.Descending}}), #"Table Buffer" = Table.Buffer(#"Sorted Rows"), #"Removed Duplicates" = Table.Distinct(#"Table Buffer", {"User ID"}) in #"Removed Duplicates"
The trick is the #"Table Buffer" line, which you have to hand-type.
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1MTYyVNJRstC30DcyMDQHMg0NgISfv4K/t1KsDnYlRqhKDI2MTUzN0JSYEFRiaAAyBo81Fkjy2Awwhjvj0AKcikyMEQ6JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Date = _t, Grade = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Date", type date}, {"Grade", Int64.Type}, {"Status", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Status", Order.Descending}}), #"Table Buffer" = Table.Buffer(#"Sorted Rows"), #"Removed Duplicates" = Table.Distinct(#"Table Buffer", {"User ID"}) in #"Removed Duplicates"
The trick is the #"Table Buffer" line, which you have to hand-type.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |