So you are working with multiple tables with PowerPivot, press the create relationship button and then you get the dreaded “No relationships detected”:
What can you do ? Here are some tips to troubleshoot you relationships:
- First thing to check is that you have 2 columns that can be related, columns like a productcode or Orderdate. Make sure your key is unique for each row in the table.
- A relationship is always between a table and a lookup table. Think of a lookup table as a dimension table. A lookup table has to contain unique values with a unique key (like a Primary Key in a database). When you want to join multiple tables always use a lookup table in between.
- Make sure your columns have identical (or similar) names, PowerPivot uses the name to automaticallyrelate columns
- When you have multiple columns that make a key you should create a new column, use CONCATENATE or & to create a composite column, and that will serve as your key.
- You want to create a relationship between two tables that doesn’t contain a unique key:
You can createa lookup table by copying the keys to a Excel sheet and use the Excel remove duplicates function to create a unique table, use create linked table to load te data to PowerPivot. Then use this table as intermediate table. - When relationship detection is to no avail, you can try to add a relationship by hand in the PowerPivot window. Some relationship will not be detected automaticly although they are valid (like Boolean, datetime or currency type colums)
- You can only create one relationship at the time between two tables, like the adv works FactInternetSales: OrderDate, DueDate, and ShipDate that joins with Dimdate. To be able to do this in PowerPivot create multiple copies of the Dimdate table.
- I want a Many-to-Many relationships in PowerPivot, Marco Russo created a blog post describing this in detail.
- You cannot create a realtionship loop, for example Table1 -> Table2. Table2 -> Table3. A relationship between Table1 -> Table3 is not possible.
I hope this post will help you solve your relationship problem.
HI Casper,
I am from South Africa. I really would like to know where I can get more information on PowerPivots in general, and specifically PowerPivot Relationships. I feel that there hasn’t been a lot of focus on this subject, which there should have been. I want to find out more with regards as to how PowerPivot handles relationships, which types of relationships one can create, etc., etc. However, your blog on “Troubleshooting PowerPivot Relationships” was very valuable to me, thax for that.., but my question still remains, where can I find more info like this?
Freddie
Hi Freddie,
I would start by searching MSDN.
Kasper
I did make correct relations in Powerpivot, but I ran into this error in my Pivot many times.
I think the relations just don’t work at all if you use content that comes from a SQL database.
The only result I found so far(which is not ideal at all(in case of updating data) is that you add ‘related’ collumns inside the powerpivot itself and then make the pivot table in Excel.
There seems to be an issue with SharePoint list data as well. I have master-detail relationships in SharePoint, and even after manually defining the relationships in PowerPivot, I still can’t get the relationships to be recognized within Excel. PowerQuery works, however.