Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
@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:
Hello,
Reacted Date should not be in the grouping but only in the new column part.
Regards,
ElenaN
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |