I am working on Data Modelling as per Week 2 Challenge instructions.
I have broken the Flat File into Data Tables (I am attaching pbix)
My doubt is with LOCATION table:
(i) I have taken Postal Code as primary key in this table and trying to map it with Orders Table.
(ii)Postal code is unique in Location Table then why its popping like “Create Relationship”
(This relationship has cardinality many to many)
The way you removed the duplicates was by applying it over all the fields of both the tables i.e., Locations as well sa Products so it didn’t removed the duplicates from the ID’s section and thereby considered it as unique values for that entire line item.
By doing so, it retained the duplicates under the field “ID” in both the dimension tables. Below are the screenshots provided for the reference -
In the Power BI Accelerator 2 Tasklist, it was mentioned by the host of the Accelerator that the participant need to remove the duplicates from the ID’s itself thereby removing the “Second Product Name” which is available against that same Product ID. Below is the screenshot of the requirement provided for the reference of Accelerator 2 program.
The above requirements is applicable for the Postal Code as well. So once you remove the duplicates from the “ID’s” column of both the tables, you’ll be able to create the “One-to-Many Relationship” between the Dimension and Fact tables.
Note: In case you want to retain those duplicated lines of Products and Postal Code, you can assign new codes to them by yourself, if you wish to do so. But that’s not the ask here. So you can choose either to “Remove Duplicates” or “Retain it by assigning new codes to them” in order to solve the data modelling task.
Hoping you find this useful and meets your requirements that you’ve been looking for.
