Create many-to-many relationship.

[Balu_18]

Hi Everyone,

I'm preparing a dashboard which requires to get the data from multiple columns from different revenue and cost and other files. I have created relationship using Bridge table among all other reports since other tables have duplicate values which should be there to do analysis. However, I'm not able to get the data from other files to create a dashboard. Could someone help me to create Bridge file.

Ex: 1. Bridge file has a column of primary key which connects all other files.
2. wanted to get data from 2 columns to dashboard (Pivot table) say, Quarter, Revenue amount from Revenue file, Cost amount from cost file
However, excel throwing need to create relationship between two tables.

 

[Monty]

My perspective is to create a Bridge file in Excel for your dashboard:

1. Ensure that each source file (Revenue, Cost, etc.) has a common unique identifier (primary key). This identifier will be used as the linking field in your Bridge table.

2. Create a new worksheet for your Bridge table. In this table, have a column for the common primary key and any additional information you want to use for analysis.

3. Import the data from each source file into separate worksheets in your Excel workbook.

4. In the Bridge table, populate the common primary key column with unique identifiers that match those in the source files.

5. For each source file, create relationships between the Bridge table and the source tables using the common primary key. To do this, go to the "Data" tab, click "Relationships," and establish connections.

6. Now, you can create a PivotTable using the Bridge table. Include the Quarter, Revenue amount from the Revenue file, and Cost amount from the Cost file in the PivotTable.

7. Excel may prompt you to create relationships between the tables if it hasn't been done already. Follow the prompts to establish these relationships.

8. Once the relationships are set up, you should be able to analyze data from multiple columns in different files seamlessly within your dashboard.

Remember, each source file needs to have a unique identifier that corresponds to the primary key in the Bridge table for relationships to work effectively. Adjust the steps based on the specifics of your data and Excel version.

 

[Balu_18]

My perspective is to create a Bridge file in Excel for your dashboard:

1. Ensure that each source file (Revenue, Cost, etc.) has a common unique identifier (primary key). This identifier will be used as the linking field in your Bridge table.

2. Create a new worksheet for your Bridge table. In this table, have a column for the common primary key and any additional information you want to use for analysis.

3. Import the data from each source file into separate worksheets in your Excel workbook.

4. In the Bridge table, populate the common primary key column with unique identifiers that match those in the source files.

5. For each source file, create relationships between the Bridge table and the source tables using the common primary key. To do this, go to the "Data" tab, click "Relationships," and establish connections.

6. Now, you can create a PivotTable using the Bridge table. Include the Quarter, Revenue amount from the Revenue file, and Cost amount from the Cost file in the PivotTable.

7. Excel may prompt you to create relationships between the tables if it hasn't been done already. Follow the prompts to establish these relationships.

8. Once the relationships are set up, you should be able to analyze data from multiple columns in different files seamlessly within your dashboard.

Remember, each source file needs to have a unique identifier that corresponds to the primary key in the Bridge table for relationships to work effectively. Adjust the steps based on the specifics of your data and Excel version.

Hi Monty,

I highly appreciate you for taking time and efforts to provide much detailed and comprehensive response. I have done all the points that you've mentioned above. However while creating pivot and using multiple columns from Cost and revenue, it throwing me error as " Relationship between the tables may needed".


The Relationship like between Bridge table and Revenue file is one-to-many
The Relationship like between Bridge table and Cost file is one-to-many

Provided Diagrammatic view below