topic Re: Remove Duplicates Per Group (keep first) measure help in DAX Commands and Tips

Remove Duplicates Per Group (keep first) measure help

Anonymous — Tue, 30 Jun 2020 11:31:55 GMT

Hi All

Is it possible to create a measure in PBI that remove duplicate values per group, and keeps the first occurrence of the value? Perhaps not a measure but maybe a new table, I'm unsure of how to work around this. For context, the database I'm working with has a funky treatment of some data that has a function back-end but doesn't make sense when trying to visualise user behaviour.

Currently, the data looks like:

USER	ENTRY ID	ENTRY	STATUS	TIME SUBMITTED (HH:mm:ss)
userA	1	unicorn	fail	10:23:10
userA	2	forest	pass	10:30:49
userA	1	unicorn	fail	10:30:49
userB	1	unicorn	fail	13:40:22
userB	1	fairy	pass	13:43:59

I want to clean it so it looks like:

USER	ENTRY ID	ENTRY	STATUS	TIME SUBMITTED (HH:mm:ss)
userA	1	unicorn	fail	10:23:10
userA	2	forest	pass	10:30:49
userB	1	unicorn	fail	13:40:22
userB	1	fairy	pass	13:43:59
...	...	...	...	...

Note the row I want to remove has

duplicated ENTRY from the first instance for userA and
duplicated TIME SUBMITTED from the first instance for userA

Also, the ENTRY ID cannot be used.

Any pointers would be greatly appreciated 🙂

Re: Remove Duplicates Per Group (keep first) measure help

Pragati11 — Tue, 30 Jun 2020 12:30:17 GMT

Hi Anonymous ,

Check if this existing thread helps:

https://community.powerbi.com/t5/Power-Query/Remove-duplicates-keeping-the-most-recent-row/m-p/757837

Thanks,

Pragati

Re: Remove Duplicates Per Group (keep first) measure help

Anonymous — Tue, 30 Jun 2020 13:38:21 GMT

Yeah... That's a perfect job for Power Query. You can do it in DAX as well as a calculated table but it really should be performed in PQ as this is the data-munging tool. I can create some sample M code for you so that you can see how such cleaning is done...

Best
D

Re: Remove Duplicates Per Group (keep first) measure help

Anonymous — Tue, 30 Jun 2020 13:49:42 GMT

Here's the M code that does what you want:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi1OLXJU0lEyBOLSvMzk/KI8ICstMTMHJGhgZWRsZWigFKuDUGkEks8vSi0uATIKEouLIQqNDaxMLFEU4jISVaUTTpXGViZA+40wVALliyqR7AaqM7YyBZoYCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, EntryID = _t, Entry = _t, Status = _t, Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"EntryID", Int64.Type}, {"Entry", type text}, {"Status", type text}, {"Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "FirstTimeEntry", each List.Min( Table.SelectRows( #"Changed Type", (r) => r[User] = [User] and r[Entry] = [Entry] )[Time] ) = [Time]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([FirstTimeEntry] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"FirstTimeEntry"}) in #"Removed Columns"

Best

Re: Remove Duplicates Per Group (keep first) measure help

Anonymous — Tue, 30 Jun 2020 14:52:24 GMT

Thanks Pragati11 , the buffer got me halfway there! Now it's just removing the wrong duplicate, hopefully the other reply will resolve this 🙂

Re: Remove Duplicates Per Group (keep first) measure help

Anonymous — Wed, 01 Jul 2020 10:10:01 GMT

Thanks Anonymous, had to have a little play around and add in r[entry id] = [entry id] but it's working well now 👍 cheers again