Joining Three Tables (Reporting) in Excel using PivotTable, Power Query or MS Query

Question

I have three Worksheets in a Excel Workbook

1-sht_Customers 2-sht_Quotes 3-sht_Contacts

Each of the Worksheets contain one table each.

1-tbl_Customers 2-tbl_Quotes 3-tbl_Contacts

The data of the each table is such

1-tbl_Customers

2-tbl_Quotes

3-tbl_Contacts

Relationships have been created between the tbl_Customers (primary key = pk_Customer_Id) and tbl_Quotes (Foreign Key = pk_Customer_Id) as one to many.

Relationships have been created between the tbl_Customers (primary key = pk_Customer_Id) and tbl_Contacts (Foreign Key = pk_Customer_Id) as one to many.

This creates an Excel Data Model as

I have tried to generate a report by using MS Query, PowerQuery and Pivot table as below (the below report is manual, copy/paste) but none of these 3 options give accurate results. In fact the returned results are repetitive.

How would I achieve to make such as report as above using either of the 3 options.

Answer 1

First, make sure you are using Customer ID from the customer table. The report won’t work if you use Customer from the other tables. It’s best practice to hide these columns in the model so they are not used.

Second, there is no relationship in the model between quotes and contacts. That means if there are multiple contacts for a customer, all of them will be listed for each quote. If that’s not the case, you will need to store Contact ID on your quotes and move the relationship there.