Forum Discussion

greglauder's avatar
greglauder
New Member
8 years ago
Solved

Merging Two Tables

Hi

 

I'm having trouble merging information into one table on power BI. 

 

I have 3 tables which I work from which I work from

 

1. Policy

2. Motor Drivers

3. Motor Vehicles

 

They all have a common field which is the unique policy reference number e.g. GREG-1001. I've managed to create the following 2 relationships:

 

  • Policy & Motor Drivers
  • Policy & Motor Vehicles

However when I try to create a relationship between Motor Driver & Motor Vehicle it comes up with the an error message stating "Can´t create a relationship between two columns because one of the columns must have unique values"

 

In the motor drivers column there can be more than one row per unique reference number if there is more than one driver.

 

Can anyone help regarding this?

 

Thanks

Greg 

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    if you're trying to normalize the data into 1 table, I recommending using the "Merge Queries" button in Query Editor to perform the merge and get the columns that you need into 1 data table.

     

    Relationships in Power BI should be used as a way to link a dimension table to a fact table.

     

    It sounds like you have 2 fact tables, and these should never have a relationship joining them.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    if you're trying to normalize the data into 1 table, I recommending using the "Merge Queries" button in Query Editor to perform the merge and get the columns that you need into 1 data table.

     

    Relationships in Power BI should be used as a way to link a dimension table to a fact table.

     

    It sounds like you have 2 fact tables, and these should never have a relationship joining them.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi greglauder,

     

    I guess you have created a one to many relationship for Policy & Motor Drivers and Policy & Motor Vehicles. That case, there existing a indirect many to many relationship between Motor Drivers and Motor Vehicles. It is not able to establish a many to many relationship. So, if you want to merge Motor Drivers and Motor Vehicles, you could try ChrisHaas's suggestion that use the "Merge Queries" button in Query Editor.

     

    Regards,

    Yuliana Gu

  • anandraman's avatar
    anandraman
    Helper I
    8 years ago

    Try Policy as BRIDGE TABLE in relationship

     

     

    POLICY MOTOR------POLICY-------------POLICYVEHICLE

                                M:1                   1:M

     

    And while creating the report pull the policy Number from POLICY table as well.