Help combining spend and sales tables

[davepdc]

I have source two tables I'd like to combine into a third table. The first table tracks campaign spend for two products by day. The second table has resulting sales of those products, also by day. Sales may occur on days where no spend occured (we have an ad running day 1, but response isn't until day 2 for example).

What is the best way to generate table C? I've come up with ways to merge on a key based on date-campaign-sku, but then I end up missing sales on days with no spend (or spend on days with no sales). What would be the correct way to create the new table in Power BI?

Thank you!

[amitchandak]

@davepdc , One option is to have a full Outer join. But I would advise Append and aggregate.

As one column name is diff. The append will add that as a new column. Now go do aggregate in Power query or you can leave that to visualization

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table