Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Bridge Table to bypass Many-to-Many Problem

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. 

Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    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.

8 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

     

    Hi Anonymous  

    could you please post (link to) sample data?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

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

       

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

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

         

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago
    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        PaulDBrown
        Community Champion
        6 years ago
        Anonymous
        Before you append the new tables, make sure both columns have the same name (sorry, I forgot to mention that)
  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Is this problem solved?

     

     

    Best Regards,

    Icey