Merging Data Sets & Getting Duplicates

[booknerd_87]

Hello! I'm pretty new to learning PowerBi and I'm hoping someone can help me with a problem I've been encountering with merging data sets. I am trying to merge two data sets (both excel files). One data set includes ad campaign information broken out by month. It also has data like spend, clicks, and leads. The second data set includes the revenue and opportunites from those ad campaigns. It also has data like the date the opportunity was created and the date it was closed. The ad campaign name is the column I am using to join the two tables. The problem is that when I merge the data (I am doing a left outer join with data set one as the first table), and then expand the second table, it duplicates all the data from the first table. I think the reason it's doing this is because there is not a one to one ratio with the data. Many opportunities can be created from the same ad campaign. Is a merge like this even possible? The end goal is to get a dynamic overview of ROI as it pertains to the month we spent the budget and the dates we created/closed an opportunity. I've includes some snapshots of sample data if that helps. First is of Table 1 and the second is of Table 1 

Table 1

Table 2

[booknerd_87]

Thank you! This worked. 

[lbendlin · Accepted solution]

Try to do this without merging. Load both tables into Power BI and then join the tables via the data model.