hi, i'm following the course and trying to create relations ship on date between Order and Dates. but it says many to many relations. pls see attachment
Chen,
There's no attachment.
Phil
Hi Chen,
The Date table should only contain one record for each date in the year(s). If you're getting a many to many error then the Date table must contain duplicates.
Mynda
hi, i think i see where went wrong. the direction of relationships are opposite of what was shown in the course. pls see below. and thats why excel detected many to many because the Order date on tab 'Order' has duplicated lines.
how do i change the direction?
this also happened to the sample file when i do auto-detection following 6.03.
| Active | Table 1 | Cardinality | Filter Direction | Table 2 |
|---|---|---|---|---|
| Yes | Customers [Province] | Many to One (*:1) | << To Customers | Regions [ProvinceID] |
| Yes | Orders [Customer ID] | Many to One (*:1) | << To Orders | Customers [Customer ID] |
| Yes | Orders [Order ID] | Many to One (*:1) | << To Orders | Returns [Order ID] |
| Yes | Orders [SKU] | Many to One (*:1) | << To Orders | Products [SKU ID] |
Hi Chen,
To delete a relationship just click on the connector line in the diagram view and press the Delete key.
Mynda
Hi Chen,
I looked at the file you sent via email and I can see the problem is that the Excel Table on the 'Dates' sheet in the file extends to the very last row in the worksheet, row 1,048,575! So, you have loaded 1,048,575 dates into Power Pivot but most of them are blank/empty cells.
You need to resize the Excel Table in the Dates sheet so it only extends to the last date on row 1419 (Table Design tab > Resize table). Then you can refresh the link in Power Pivot so that the excess rows are removed from the model.
Then you can insert your relationship.
Mynda
yes the relationship is now in place. many thanks. 2 more questions,
1, do you know what i could have done wrong when creating the Table? I think I followed the exact step from the course but it automatically extended the the last row in t he sheet.
2, any comment on why i failed uploading the attachment
Hi Chen,
No idea why your Table was created for all rows as opposed to just the occupied cells. Maybe you accidentally edited the range before inserting the table.
I'd say the file upload issue was because the file was too large.
Mynda
