In this article, we will talk about how to manually create a relationship by identifying the primary and foreign keys if Power BI is unable to identify the correct auto-relationship between the tables after we import a dataset Sample Sales Data.

Preparing the Dataset

In the first step, I will connect with an Excel workbook by clicking on Get Data. To learn more about the data connector in Power BI, please click on this link: Data sources in Power BI Desktop.

Once I click on the Excel workbook, the new window will open. I will click on sales sample data from my local computer and click open. Here is the sample data Sales Sample Data.

In the next step, Power BI navigator will open. Since I required all the four tables Customer, Product, Region and Sales, I will check mark all of them. On the bottom right click to load inside Power BI.

Once you imported the Sales Sample Data , the Power BI will auto detect the relationships between the tables . Once we click on model view, so it will look like below screenshot.

Note: If you noticed there is relationship between sales table to customer table but not region and product table. Since Sales is our master or main table,  it needs to have relationship between all the four tables. We need to analyze our data to established a relationship.

Analyzing missing relationship tables

In order to analyze the product table, we need to click on table view as per the image below. You will notice that the product table has an Index column with unique codes that can be our Primary key. Similarly, we will see the same in region table as well. You can learn more about primary and foreign key concepts here: Difference between Primary Key and Foreign Key.

After analyzing product and region table and identifying their primary key, we have to see if similar keys are present in sales table. We can see both the index columns as primary keys from the product and region tables present in the sales table as the delivery_region_index column and the product_description_index column as foreign keys. You can see these links in the image below.

Once we identified the columns need to create relationships, we will click on manage relationships and create 1-many relationships from the sales table(many) to all other 3 tables. Those tables are the customer, product, and region tables.

Finally, our data model should look like as per the screenshot below. For detail understanding of Power BI relationships Create and manage relationships in Power BI Desktop and Model relationships in Power BI Desktop.

The auto-detect relationship is unable to identify essential joins between tables; therefore, specific procedures must be followed for diagnosis.

• We found the unique column in product and region table which is also the primary key.
• We identified the same two columns in sales table as a foreign key.
• Once we identified primary key and foreign key we have joined the sales table with region and product table with 1 - to - many relationship.