Forum Discussion

imrangaslam's avatar
imrangaslam
Frequent Visitor
5 years ago

How to Normalize these two tables

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

YearMonthDevice categoryUser typeDateChannel groupSessionsRevenueOrders
20202DesktopNew Visitor01/02/2020Organic Search15500
20202DesktopNew Visitor01/02/2020Paid Search4500
20202DesktopNew Visitor01/02/2020Referral813.491
20202DesktopNew Visitor01/02/2020Social900

 

Table2

YearMonthDevice categoryUser typeProduct nameDateChannel groupProduct BrandProduct Category (Enhanced Ecommerce)RevenueOrders
20201DesktopNew VisitorBitzy Biter Teething Ball & Training Toothbrush - Lemon Drop22/01/2020Organic SearchItzyRitzySilicone Teethers14.991
20201DesktopNew VisitorBitzy Biter Teething Ball & Training Toothbrush - Lemon Drop26/01/2020Paid SearchItzyRitzySilicone Teethers14.991
20201DesktopNew VisitorBitzy Biter Teething Ball & Training Toothbrush - Lemon Drop30/01/2020Organic SearchItzyRitzySilicone Teethers14.991
Tips and Tricks

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    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.