Merging Two Tables

[greglauder]

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 

[anandraman]

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.

[v-yulgu-msft]

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

[Anonymous · Accepted solution]

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.