Platform Banner

Power BI - Manage relations between data sources Tutorial

In this video, you will learn how to manage relationships between data sources in Power BI.
The video covers the process of importing data from different sources, creating manual relationships between models, and automatically creating relationships based on field names and types.
This knowledge will help you effectively connect and analyze data in Power BI.

2:37
3252 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to manage relationships between data sources in Power BI, focusing on importing data, creating manual and automatic relationships, and understanding cardinality types.

Chapitres :

Introduction to Data Source Management in Power BI
In this section, we will explore how to effectively manage relationships between different data sources in Power BI. Understanding these relationships is crucial for creating comprehensive reports and analyses.
Importing Data from Excel
To begin, navigate to the report view and switch to the model view. Here, you can import data from Excel. For this demonstration, we will import two databases: 'employees' and 'sales'. Once imported, these models will be visible in the model view.
Creating Manual Relationships
After importing the databases, you can create a relationship between the two models manually. For example, connect the 'employee ID' from the employees database to the corresponding metric in the sales database. This can be done by dragging and dropping the relevant fields. Once connected, an arrow will appear, indicating that a relationship has been established. Hovering over the arrow will show the connected elements.
Understanding Data Types
It is essential that every field involved in a relationship has the same type of data, either numeric or text. This consistency is necessary for creating valid relationships between data sources.
Creating Automatic Relationships
Next, we will import another database to demonstrate how automatic relationships are created. For instance, when importing a 'product category' database, ensure that the field names match and contain the same type of values. When you hover over the 'product name' field, you will see that an automatic relationship has been established.
Exploring Relationship Properties
By right-clicking on the relationship arrow, you can access the properties of the relationship. Here, you can view and adjust the cardinality, which defines the type of relationship between the data sources. The cardinality options include: - Many to One - One to One - One to Many - Many to Many The term 'many' is represented by an asterisk (*). Among these, the most common type is the One to Many relationship.
Conclusion
In summary, managing relationships between data sources in Power BI is a vital skill for data analysis. By understanding how to import data, create both manual and automatic relationships, and recognize the types of cardinality, users can enhance their reporting capabilities and ensure accurate data representation.

FAQ :

What is Power BI used for?

Power BI is used for data visualization and business intelligence, allowing users to create reports and dashboards to analyze data from various sources.

How do I create a relationship between data sources in Power BI?

You can create a relationship by dragging and dropping a field from one table to a corresponding field in another table in the Model View.

What types of relationships can I create in Power BI?

In Power BI, you can create one-to-one, one-to-many, and many-to-many relationships between data sources.

What does cardinality mean in Power BI?

Cardinality refers to the type of relationship between two tables, indicating how many records in one table correspond to records in another.

What should I do if the field names do not match when creating a relationship?

Ensure that the field names and data types match in both tables to create a relationship. If they do not match, you may need to rename fields or adjust data types.

Quelques cas d'usages :

Sales and Employee Data Analysis

A business can use Power BI to analyze sales data in relation to employee performance by creating relationships between sales records and employee IDs, allowing for insights into which employees are driving sales.

Product Category Insights

Retail companies can import product category data and sales data into Power BI, creating relationships to analyze sales performance by product category, helping to identify trends and optimize inventory.

Customer Relationship Management

Organizations can link customer data with sales data in Power BI to better understand customer behavior and preferences, leading to improved marketing strategies and customer service.

Financial Reporting

Finance teams can create relationships between budget data and actual spending data in Power BI, enabling them to track performance against financial goals and make informed decisions.

Glossaire :

Power BI

A business analytics tool by Microsoft that enables users to visualize data and share insights across their organization, or embed them in an app or website.

Data Source

A location from which data is obtained, which can include databases, Excel files, online services, and more.

Model View

A view in Power BI where users can see and manage the relationships between different data sources and tables.

Relationship

A connection between two data tables that allows for data from one table to be related to data in another, enabling combined analysis.

Cardinality

The type of relationship between two tables, indicating how many instances of one table relate to instances of another. Common types include one-to-one, one-to-many, and many-to-many.

Asterisk (*)

In the context of cardinality, it represents the 'many' side of a relationship, indicating that multiple records in one table can relate to a single record in another.

Drag and Drop

A common user interface action where a user clicks on an item and moves it to a different location, often used to create relationships in Power BI.

00:00:03

between data sources in Power BI.

00:00:08

Get your data from here.

00:00:10

When you click on "more" you will see

00:00:12

all the possible selection of data

00:00:14

sources and you can use a combination.

00:00:20

From the report view,

00:00:21

I will now go to "Model view".

00:00:25

And I will import data from Excel.

00:00:31

Once I have imported my two databases

00:00:35

from Excel employees and sales,

00:00:38

the models appear right here.

00:00:40

Sales and employees.

00:00:42

I can create a relationship

00:00:45

between these two models manually.

00:00:48

For instance, I can connect the

00:00:52

metrical number to employee ID.

00:00:55

By doing a drag and drop and automatically,

00:00:58

the relationship is created when

00:01:00

I hover over the arrow I can see

00:01:04

the two elements are connected.

00:01:06

Now every field must have the

00:01:08

same type of data underneath it,

00:01:11

either a numeric value or text,

00:01:13

for instance to create the relationship.

00:01:21

Now I'm going to import another

00:01:24

database and this way we'll see how

00:01:27

an automatic relationship is created.

00:01:29

Product category => open.

00:01:43

Both field names must

00:01:44

be the same with the same type of values.

00:01:48

And you see, when I hover over

00:01:50

it product name to product name,

00:01:52

the automatic relationship has been created.

00:01:54

When you right click on the

00:01:56

arrow you can go to properties.

00:02:02

But the cardinality is the type

00:02:05

of relationship and right here

00:02:08

you can see that you have " many to one", "One to One"

00:02:11

or "One to many" or "many to many".

00:02:14

The word many is represented by the Asterix.

00:02:24

Out of the four cardinality's.

00:02:27

The most common one is "One to many".

00:02:30

Now you know how to manage

00:02:34

relationships between data sources.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

4900 Views

Microsoft Power BI Video Training – Advanced

Dashboard, indicators (KPIs), activity tracking are part of your daily vocabulary? Discover how to quickly glean insights from your data usi...

ALL COURSES