Power Pivot Possibly?

[Gesyca_is_joy]

So I have to create what will be in essence a small database in excel for reasons far too long to go into. My thought is to create 3 tabs with 3 named tables then create a power pivot to drill down and return the needed information. If there is a better way to do this PLEASE feel free to tell me. (Excel on PC)

Table 1 has vendor profiles information (think company name, contact info, etc.):

Company Name	Vendor ID	Name	Phone	Email
ABC Services	S-11115	John Smith	555-5555	john@abcservices.com

Table 2 has work types (trades) that each company can do:

Vendor ID	Electrical	Plumbing	HVAC	General Repairs	Landscaping
S-11115		Yes	Yes

Table 3 has locations each company can work at:

Vendor ID	Site 123	Site 456	Site 789	Site 1010	Site 999
S-11115		Yes	Yes

I tried to connect the tables together using the Vendor ID field as it is unique, no duplicates and in all tables

my ultimate goal is to create a pivot that will let the user filter on site and trade then return the company name and contact information for all companies that can do that work at that location, something like:

Site: [dropdown of all sites]
Trade: [dropdown of all trades]

Company Name	Contact	Phone	Email

But I cannot get it to work and I am wondering if it's because the sites and trades are column headers?

 

[Alex Blakenburg]

I personally thing a Pivot it the wrong tool.
Pivot's normally revolve around a "Fact" table where a fact table is the central table which stores quantitative data.

I don't think any of your tables contain quantitative data.

My thoughts would be to pull all 3 files into Power Query. Unpivot the Location table.
Then
Option 1)
Output the Merged data to a Excel.
This will output as a table
Add slicers for Site & Trade to the table

Option2)
Have drop down boxes for Site & Trade
Then refresh the query using Site & Trade as parameters to only return the filtered data to Excel

 

[Gesyca_is_joy]

Agreed. After writing out the help question and staring at my screen for a while I inverted my dependent tables so the site and trades are drop downs. The. Created one to many relationships and got it to work.

 

[Alex Blakenburg]

Thanks for the update. Glad you were able to get it working.