Bridge Table to bypass Many-to-Many Problem

[Anonymous]

Hello community, I am hoping you can help me. 

I have one column in Table A with part numbers, all unique except for a string of 0's afterwards. Those zero's are there because in the source data there is room so that if a new part number is added, it will overwrite that 0. 

I have one column in Table B with part numbers, this time duplicating because they represent each shipment the part number is on. 

I want to create a relationship between these two tables on the part number column, but since i need the 0's incase new part numbers are added (this is an autonomous report) it will not let me do so since neither column has a unique number. 

I am trying to create a bridge table, but am stck. I want to have a new table that combines part numbers from table A and table B, removing any duplicat values leaving me one table that i can then use to create a relationship with. 

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this is what you want:

1. Create a calculated table.

Bridge Table =
DISTINCT (
    UNION ( VALUES ( 'Table A'[Part Numbers] ), VALUES ( 'Table B'[Part Numbers] ) )
)

2. Create relationships.

Best Regards,

Icey

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

[PaulDBrown]

@Anonymous
In Power Query, Create new tables from each by referencing them, remove all columns except the relevant, append both new tables, remove duplicates, load and create the relationships

[Anonymous]

Hi @ImkeF - Since both Table A and B contain duplicates... the bridge table would display all part numbers and the one 0 with no duplicates. 

That way Table A can have a relationship with Bridge table and, 

Table B can have a relationship with the Bridge Table. 

hi @PaulDBrown I will try that, thanks!

[PaulDBrown]

@Anonymous
Before you append the new tables, make sure both columns have the same name (sorry, I forgot to mention that)

[ImkeF]

Hi @Anonymous  

could you please post (link to) sample data?

[Anonymous]

I cannot post the actual data, but here is an example. 

[ImkeF]

Thanks, but I still don't understand the logic. How should your bridge-table look like / where's the many-2-many here?