Powerpivot lookup table to two different tables that are themselves connected - best way to model?

[Vaslo]

I have three tables. I'd like to work with them using data connections in Powerpivot so I can create simple Pivot tables. The tables are:

• One is a fact table with the usual customer, product number, sales, gallons, etc.
• The second is a lookup table which allows me to lookup a "cost center" based on a concatenation of the product and the customer across the fact table and this one (some products hit different cost centers depending on the customers).
• The third is just a simple table that corrects the mess of customer names to very simple groups. For example a customer might be in the system as ABC, ABC inc, ABC Corp, etc. I use this table to make it ABC for all of these.

My problem is that both the fact table and the cost center lookup table have customer names, and both are in horrible condition. The cost center table is a lookup table for the fact table if it wasn't clear. When I connect the fact table and customer tables together to get the cost center it is fine but because of all the mismatches, a lot gets missed. However, when I add in the customer cleanup table to both of those tables in order to clean up the customer names it shuts down my link between fact and cost center, and I can't really link through the customer table.

Is there a better way to do this? Or do I need to cleanup the customer names before I even get into Powerpivot? Thanks.

 

[GraH]

Hi, maybe you can first do the "clean-up" in Power Query with your transformation table that corrects customer names?
I would first try with a simple Left Outer Join, since you might have corrected all customer names?

Then there would only be 2 tables in the model.