Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Editor: how to remove duplicates selectively based on the condition

Hi All,

 

I use PowerBI to analyze efficiency of our mass email campaigns. I get a table that contains all emails that were sent.

 

Each row is for one email message to one email address, e.g.:

 

first@gmail.com  1st email 4/5/19

first@gmail.com 2nd email 4/7/19

first@gmail.com 3rd email 4/9/19

 

I aslo see if the email was openned, clicked, replied (I have the corresponding date and time columns. they are empty if no action).

 

I have a column with the email number I sent, e.g. 1 for the first email, 2 for the second etc.

 

Also, I have a column that aggregates dates of the interaction with my emails (either open or click or reply or unsubscribe date).

 

I want to get a list with unique email addresses with the lattest interaction (if any happened).

 

I tried to sort by filter by Interaction date and then remove duplicates in Editor. However, I always get the list with the number 1 email in the list.

 

I use this table to merge with the other one so it is important for me to have the unique emails, but it would be awesome if I could get the latest interaction date for further analysis.

 

Any ideas how to accomplish it?

 

5 REPLIES 5
ElenaN
Resolver V
Resolver V

Hello,

 

It feels difficult to answer without a clear table structure, but one idea you can try in Power Query would be to make a Group by email address and define the new column as Max of Interaction Date.

 

Regards,

ElenaN

Anonymous
Not applicable

@ElenaN , thank you, but it didn't help. However now I know about this feature 🙂

 

I still get the list with the only first email

 

Here is what I did:

 

Screenshot_1.png

Hello,

 

Reacted Date should not be in the grouping but only in the new column part.

 

Regards,

ElenaN

Anonymous
Not applicable

Elena, thanks! Well, I could get a list of email addresses and date, but if I add more fields (which I need), this solution stops working for me 😞

 

I need to get a list of emails with three more columns: subject, email number and reacted date

 

 

Hello,

 

So, under Group By section you should pick the Email, Subject and Email # fields and under New column name you should have MAX Reacted Date.

 

If you have these, what happens? What is your end result?

 

Regards,

ElenaN

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.