Your business might benefit from the table merging features available in Power BI: manage relationships.
Let’s take an example with the CRM table below, which contains "city" information which is also available in the Cities table. Merging these 2 will enable us to make tables and graphs using all information contained in these 2 tables.
To do this, let’s go into the "Relationship" view.
Once you’ve merged two tables with a relationship, you can use the data from both tables as if it were a single table. In this way, you won’t have to worry about details of the relationship, and you won’t need to flatten these tables into a single table before importing them.
In many cases, Power BI Desktop can automatically create relationships for you. Nonetheless, if Power BI Desktop cannot determine with a high degree of certitude that a relationship between two tables must exist, it will not automatically create a relationship. In this case, you’ll have to create it yourself.
By clicking on the "Relationship" view, different potential relationships are automatically displayed, showing relations between one or several tables with a single-direction or bi-directional arrow.
To customise other relationships or edit existing relationships, click on "Manage Relationships".
A window will open. Click on "New".
A new "Create Relationship" window will open, letting you select tables between which to create a relationship.
In the drop-down list, choose among the created tables, and click on the columns to merge. Selected columns appear in grey.
It is now very important to choose the best cardinality.
Many-to-One (*:1): This is the default type, and the most common. This means the column in one table can have more than one instance of a value, and the other related table, often known as the Lookup table, has only one instance of a value.
One-to-one (1:1): This means the column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value. For more information about modifying cardinality, please see Power BI’s online documentation (https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/ ).
When the "Make this relationship active" option has been selected, the relationship serves as the active, default relationship. If there are more than one relationships between two tables, the active relationship provides a way for Power BI Desktop to automatically create visualizations that include both tables.
For more information about making a specific relationship active, please see Power BI’s online documentation (https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/ ).
The new relationship will show up in your list of already existing relationships.
See a visual representation of relationships that have been created.
Use relationships in the "Relationship" view to visualise your creations. If you select fields from several related tables, their data will automatically be aggregated.
Here, for example, after merging the Cities and CRM tables, and then CRM and Profile, we get a dashboard listing the cities and regions of identified visitors, as well as profile qualification (beginner, average, expert).