Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Establishing relationship help

Hi,

 

Without providing all the data, I am simply looking for a solution in terms of the best approach.

 

TABLE 1: Business unit IDs with their location by STATE

TABLE 2: Supplier IDs with their coverage by STATE

 

I want to use coverage as a filter when looking at one business unit so it excludes suppliers outside their coverage.

 

The issue is coverage is setup in our database as either "National" or it simply lists the states as, eg; NSW/ACT (meaning only the supplier can only supply to those two states).

 

What is the best approach in creating a relationship between coverage and state?

If it is a bridge table... what would that look like to incorporate "National" meaning all states?

How would the table incorporate combinations of states too?

 

Thanks.

General Comment
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous.,

     

    I would unpivot the Supplier table and create a unique state table to connect to both tables.

     

    Let me know if it worked or if you need something else.

     

    Best Regards,

    Duarte Raminhos

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Thanks for the recommendation.

       

      Could you please explain what you meant by unpivot the supplier table?

       

      Also, what should the unique stable table look like?  Do I need to create a row for every combination of Coverage - State?

       

      Cheers

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Hi Anonymous

        So you have to select all the rows but the supplier attributes, inside power query you have on transform pane a unpivot button.

        Divide by the delimiter before you unpivot so it can create a column for every state and you select those columns and unpivot.

        Then you make a relationship between the supplier table and the unpivoted one, then you just make a relationship between suppliers and the company and you have it.

        Let me know if it worked if so mark as solution.

        BR,
        DR