How to Normalize these two tables

[imrangaslam]

Hello

I have these two tables. How I can do relationship so I can use for different reports in power bi.

I tried to use date table and did 1 to many but not good.

Table1(Date)

         Date Table (Date)

Table2(Date)

Any possibility to connect both table directly.

Thanks

Table1

Year	Month	Device category	User type	Date	Channel group	Sessions	Revenue	Orders
2020	2	Desktop	New Visitor	01/02/2020	Organic Search	155	0	0
2020	2	Desktop	New Visitor	01/02/2020	Paid Search	45	0	0
2020	2	Desktop	New Visitor	01/02/2020	Referral	8	13.49	1
2020	2	Desktop	New Visitor	01/02/2020	Social	9	0	0

Table2

Year	Month	Device category	User type	Product name	Date	Channel group	Product Brand	Product Category (Enhanced Ecommerce)	Revenue	Orders
2020	1	Desktop	New Visitor	Bitzy Biter Teething Ball & Training Toothbrush - Lemon Drop	22/01/2020	Organic Search	ItzyRitzy	Silicone Teethers	14.99	1
2020	1	Desktop	New Visitor	Bitzy Biter Teething Ball & Training Toothbrush - Lemon Drop	26/01/2020	Paid Search	ItzyRitzy	Silicone Teethers	14.99	1
2020	1	Desktop	New Visitor	Bitzy Biter Teething Ball & Training Toothbrush - Lemon Drop	30/01/2020	Organic Search	ItzyRitzy	Silicone Teethers	14.99	1

[v-lionel-msft]

Hi @imrangaslam ,

Please try to create such a relationship.

Best regards,
Lionel Chen

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

[lbendlin]

I would unpivot Table 1 to put sessions, revenue and orders into separate rows (or even better, into separate tables)

The user ID seems to be missing from both tables. What do you think the relationship column should be? I don't think the date is a good candidate at all.