How to merge three tables?

[Sean500]

Hi

I have three tables called Contact, Opportunity and Website

Contact:

Opportunity:

Website:

Website table has data as a daily basis (date), and Opportunity & Contact has data as Month-Year wise. 

I wanted to merge all these three tables and wanted below output and select Campign from the Website table as a filter.

Month -Year	Clicks	Cost	CTR	Session	Organic Sraches	Gross Price	Nights	Projected weeks	Opportunity	Projected Sales	ROI	Weeks Needed
Mar-22	4	£912	0	23	45	£0	0	0	45	34	0.2	2
April 202	20	£374	0	34	100	£0	0	0	45	3	2	23
May-22	26	£870	23	3	23	£35	-16	0	4	24	33	23
Jun-22	56	£3,180	15	4	45	£7	14	0	4	2	1	2

-- Clicks, Cost, CTR, Session, Organic Searches from Website table
-- Gross Price, Nights from Contact table
-- Projected Weeks and Opportunity from Opportunity table
--

Projected Sales = (SUM(Oppotunity[Projected Number of Weeks]) + SUM(Contact[Nights]))/2
ROI = Gross Price/Cost
Weeks needed = (Cost x £9)-Gross Price)/1247

Please note the above output values are dummy

I tried to merge in Power Query all these table but no success. Can anyone advise how to do this?

[bhelou · Accepted solution]

Dear , 

All you need is a date table and from it you can link all the tables together , in dates table , generate end of month and year also , 

attached Power BI , 

https://www.dropbox.com/s/tj1e3et3quhtjqb/test%20dashboard.pbix?dl=0 

[RocketRod]

For starters, in your Website table add a new column that derives the start of the month from your Date column. Refer to get start of month https://learn.microsoft.com/en-us/powerquery-m/date-startofmonth.

Use the new column to merge the other tables. Once all data us merge you may want to consider doing some of your calculations in Power Query.