How to arrange data to eliminate many to many relationship

[souchock]

I am trying to setup data tables properly to eliminate a possible many-many relationship.
I have Test Types, which each have a Test Length. 

Each Test Type affects certain Components.

Each Component has a Due Date.

I have a many-many relationship between Test Type and Components.

Like this: 

So my data tables are currently arranged like this:

I can re-arrange the tables to look like this, but then I don't have a common column to connect:

Is there a best-practice or other method to setup my data tables to properly manage these relationships?

[v-xicai · Accepted solution]

Hi @souchock ,

For the left table in your 3rd picture above, you may enter into Query Editor, select on the column Component1, Component2 and Component3 at the same time, right click to choose option "Unpivot columns", then you can rename the result  columns with "Affected Component" and "Is Affected". Don't forget to click button "Apply & Close" .

Then you can create relationship with the right table in picture on field [Affected Component] in "Both" Cross filter direction.

Best Regards,

Amy 

Community Support Team _ Amy

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

[v-xicai · Accepted solution]

Hi @souchock ,

For the left table in your 3rd picture above, you may enter into Query Editor, select on the column Component1, Component2 and Component3 at the same time, right click to choose option "Unpivot columns", then you can rename the result  columns with "Affected Component" and "Is Affected". Don't forget to click button "Apply & Close" .

Then you can create relationship with the right table in picture on field [Affected Component] in "Both" Cross filter direction.

Best Regards,

Amy 

Community Support Team _ Amy

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

[vanessafvg]

It is good practise to set up a look up tables.  A date table, a component table, test table, so just the unique values of those columns in separate tables, then link all those tables to the relevant look up table, using the look up table on your visuals to slice by. You can usually avoid many to many relationship but setting up lookups and linking those in a one to many to the transactional data

[souchock]

All 3 of these are our lookup tables. They are all fixed relationships (even the Test vs Components table).

[vanessafvg · Accepted solution]

Ok then i am unsure of what you asking, does your current relationship give you what you need or what are you missing?  Are any of these transactional data or is all just lookup data, if its all just look up data, the best thing to do in my opinion is just denormalise into one table, so in power query merge the 3 the tables together.