Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.