In this guide we’ll demonstrate how to establish connections between tables in Microsoft Power BI. The best part is that this can be done by using a simple click and point wizard, eliminating the need to write SQL command.
(This post is part the Power BI Guide. Utilize the right-hand menu to navigate. )
Relationships in Power Relationships in Power
Within Power BI, a relationship is a way to document the common elements among tables.
In the case we’ll use here, we’ll use two tables that are part of an online sales system: orders and orders.
- A common component is CustomerNumber.
- The table for customers contains the name of the customer.
- The order table includes quantity of the order, sold product and more.
If you wish to print the name of a customer in a sales report for instance, you’ll have to inform BI the columns that connect the two tables.
To follow our guide, download the following information:
Import the tables.
Types of relationships
On the screen that you imported the sources of data Click on the manage relationships button. BI will determine which elements share a commonality. Because the customername is found in two tables, it chooses the latter. It is possible to edit this in case you want to alter it.
The Edit button lets you modify how the relationships are arranged. There are three kinds:
- one-to-one. For each row in the left-hand table, there is just one row on the table in right hand.
- One-to-many. It is the most frequent scenario. This means that for every row in one table there is several rows on the table to the left. Which table you use is dependent on whether you place it either on the left or right. For our customers and orders table, we have a lot of (more than 1) rows within the customers tables for every row of the table for customers.
- Numerous-to-many. Think of this as a one-to many relationship but both directions. Imagine, for instance, that a doctor prescribes medications, but also uses medication. Many-to-many is not a term that is commonly used.
The third button to the left side and you will can see the table’s relationships.
Go on the screen for report layout, and choose the layout type table.
Customers and tables are located on the right hand side. You select the columns you would like to see on the table from each table. We choose the order details from the table of orders and then the name of the customer from the table for customers.
Since BI has a better understanding of that the order and the purchase tables relate and how they are connected, it is able to determine the name of the customer with the customer’s number from the table for orders.
The report isn’t the greatest. Click on the Focus mode within the report and zoom in and expand it.
This is where you can see that Power BI has extracted the name column of customers from the table with customers. This means that it relied on relationships to search it up.