De-duplicating data within a certain timeframe

[pselliott]

Hello,

We have 3 data sets (1. Meetings, 2. Emails, 3. Customer). We want to count if an an email was generated for a specific Customer within 35 days of the Customers meeting. 

Sometimes multiple emails will be generated for a meeting with a customer but we want to only count these emails once. Often customers have multiple meetings a year and for that reason will have an email generated for each of the meetings - we want to count these as seperate emails as they are related to seperate meetings.

How can we de-duplicate/filter so that any emails generated for a customer meeting within 35 days are counted as 1 (even if multiple are generated)?

Thank you 🙂 

[Anonymous]

Hi, 

Thanks for the solution DallasBaba offered and i want to offer some more information for user to refer to.

hello @pselliott , based on your description, you can use lIst.Distinct() funciton, you can refer to the following link about the function.

List.Distinct - PowerQuery M | Microsoft Learn

And can you provide some sample data so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

Please provide a sample table for your question

[DallasBaba]

@pselliott Can you merge the datasets in power query editor to create a unified table that includes the necessary fields (Customer ID, Meeting Date, Email Date).
Then expand the merged table to include the meeting date and email date.
And then add a custom column to calculate the difference in days between the Meeting Date and Email Date

= Duration.Days([Email Date] - [Meeting Date])

Then remove duplicate record and load the data to the power bi interface and begin your analysis.