Dublicates

[Anonymous]

Hello guys, I have been faced with the following problem today. The problem seems to me to be quite trivial, but I was quite upset today. I have a bigger table with e.g. different names like "A,B,C,D,E,....". Each of these names has different monthly events. I just want to transform this data set within Power BI, in form of a table, so that I can show the duplicates (e.g. only one A,B,C,...) that had the last event. Everything else should be hidden. These data must not be deleted, because I use them for another visual. If someone has tips I would be very happy! Greetings ABBA!

[gpiero]

@Anonymous 

Hi,

could you share some rows of data just as a sample?

Regards

[Anonymous]

@gpiero ,

this should be the solution in the visual:

Date	Notification.Code
01.10.20	A
21.09.20	B
15.09.20	C
02.05.20	D

[gpiero]

@Anonymous 

Group By could help you?

https://1drv.ms/u/s!Ah7_1Sua__g-62ICDv3FeHAtDVTK?e=GkDPQX 

[Anonymous]

@gpiero Hey,

thank you! But this just works with a smaller table like the first one I send. With a larger table like i send at least, it doestn work. Do you have any ideas to solve this?

Regards,

Abba!

[gpiero]

@Anonymous 

Hi

I put your data, all your data

and I have got the following

No data has been deleted, but it is my original criteria. There are many solutions. My suggestion is only one of many.

Please see page 3

https://1drv.ms/u/s!Ah7_1Sua__g-62ICDv3FeHAtDVTK?e=1eoyMv 

Regards

[Anonymous]

Hey @gpiero ,

thank you for you help!!

It seems like it works 👌

But can you shortly explain how you did it (maybe for dummies 😁)

Thank you very much!!!!!! ❤️

[gpiero · Accepted solution]

@Abba123

Hi

on page 3 you will find both solution proposed on this post.

First visual based on Table3 that contains all your data with the solution by @mahoneypat

Last visual on page 3 based on Table by

Table = SUMMARIZE(Table3; Table3[Notification.Code];" a"; MAX(Table3[Date]))

from Table3.

If both could satisfy your requirement please mark as solution.

Regards

[gpiero]

@Anonymous 

sorry, here the right link

https://1drv.ms/u/s!Ah7_1Sua__g-62ICDv3FeHAtDVTK?e=QyaQ2w 

[mahoneypat]

Just make a table visual with the Code and Date columns, and for the Date column choose "Latest" as the aggregation (see pic).

Regards,

Pat

[Anonymous]

He @mahoneypat ,

thank you for your help! This one works fine with the example, but if you have a bigger table like mine in reallife, its not possible. For example with this one:

Date	Notification.Code	ed	erg	grewg	qreg	qerg	qerge	qrge	qg	qrg	qrg	resume date
01.10.20	A	567	248	248	248	248	248	248	248	248	248	04.02.20
05.01.20	B	^3456	248	248	248	248	248	248	248	248	248	05.02.20
04.07.20	A	247	248	248	248	248	248	248	248	248	248	06.02.20
15.09.20	C	248	249	250	251	252	253	254	255	256	257	07.02.20
02.05.20	D	249	250	251	252	253	254	255	256	257	258	08.02.20
06.06.20	A	250	251	252	253	254	255	256	257	258	259	09.02.20
21.09.20	B	251	252	253	254	255	256	257	258	259	260	10.02.20
19.02.20	B	252	253	254	255	256	257	258	259	260	261	11.02.20
03.03.20	D	253	254	255	256	257	258	259	260	261	262	12.02.20
01.09.20	C	254	255	256	257	258	259	260	261	262	263	13.02.20

This is also not the real table, but if you try it works with a table like this, it also should work with mine.

Thank you!

Abba!

[mahoneypat]

Which columns are you using from your example table?  If just the first two columns, the approach should work.  What should the output look like from your example data?  What are you seeing?

Regards,

Pat

[Anonymous]

Hey @mahoneypat ,

i want to use all the columns in the table. But I only want to see 1x A,B,C,D,...

How can I solve this without taking out columns or just using 2 columns?

Thank you!

[Anonymous]

Hi @gpiero and other friends,

just for example:

Date	Notification.Code
01.10.20	A
05.01.20	B
04.07.20	A
15.09.20	C
02.05.20	D
06.06.20	A
21.09.20	B
19.02.20	B
03.03.20	D
01.09.20	C

And i want this table for example in a table visual in Power BI, but just want to see the Notification.Codes with the latest date, without deleting the others.

Thank you!