Scribd
Upload
253 views17 pages

Loading Data Into Power BI

Uploaded by

kachizih
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
253 views17 pages

Loading Data Into Power BI

Uploaded by

kachizih
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 17

Loading Data in Power BI

Loading data into Power BI

In this train you will learn how to:

- Load data that consists of different data types into


Power BI Desktop;

- Set up an ERD data model; Power BI


- Link data in Power BI
Outline

● The first step to visualising your


data is knowing how to load your
data into Power BI.

● Power BI is both dynamic and


versatile in the number of data
source types that it allows
connection to.

● In this train, we will focus on


loading data into Power BI that
consists of different data types.

● We will also gain an understanding


of the importance of linking data
and how to set up an ERD data
model.

Source
Load IPL Excel datasets into Power BI
Before we get started, let’s go ahead and download the files we will be working with.

● For this train we will be working with data


extracted from the IPL (Indian Premier League).

● Download the zip file with the datasets by clicking


“DOWNLOAD ADDITIONAL FILES” on Athena.
Extract the files. There should be a file called
“deliveries.xlsx” and one called “matches.xlsx”.
Load IPL EXCEL datasets into Power BI

Step 1:

● Open Power BI desktop. Alternatively, you can download it here.

Page Selector – Navigate between your pages

Sign In – By selecting ‘Sign In’ from the top right of the window this dialogue
box pops up. We will not sign in as of yet instead we will work offline.
Load IPL EXCEL datasets into Power BI

Step 2:

● Click the “Get Data” button then select “Excel”.

● In the file browser, navigate to your “matches.xlsx” and


hit the “connect” button.
Load IPL EXCEL datasets in Power BI

Step 3:

● Tick the “matches” box on the Navigator pop-up


and then click “load” (picture on the right).

● Repeat the same steps for the deliveries table.

● When you are loading your data, Power BI will


attempt to convert the data type of the source
column into a data type that better supports more
efficient storage, calculations, and data
visualization.

● Let’s go ahead and see how we can determine and


specify a column’s data type.
Specifying a data type
In Power BI Desktop, you can determine and specify a columns data type in the Query Editor, Data View or
Report View.

Step 4:

● Next we will learn how to specify a column’s data type


using the Query Editor in Power BI. We can start by
selecting Transform data on the Home tab. This will
open Query Editor in a new window in Power BI.
● In the Query Editor, we can specify each column’s data
type by clicking on each column and choosing from the
list of data types that are supported in Power BI.
● We can see from the image on the right the data type
for City is text data, which can also be referred to as
“string data” and symbolized by
● Many data types that are supported by Power BI. We
will take a look at each of these data types in the next
slide.

Take note: DAX functions require special data types for the
function to work correctly.
Data types in Power BI
Power BI supports the following Data Types:

Text types Date/Time types True/False type


A Unicode Date/time - Represents both a date and time value. True/False - A Boolean
character data Date - Represents just a date. value of either a True or
string. Time - Represents just time. False.
Can be strings,
Date/Time/Timezone - Represents a UTC date/time with
numbers, or dates
represented in a a timezone offset.
text format. Duration - Represents a length of time. It’s converted
into a decimal number.

Number types Binary type Blank type


Decimal- Represents a 64 bit (eight-byte) Used to represent any A data type that contains a
floating-point number. i.e how Excel stores its other data with a blank value. This is the
numbers. binary format. value that is assigned to a
Fixed- Has a fixed location for the decimal separator. column when the data
Whole- Represents a 64 bit (eight-byte) integer contains a NULL value.
value. Can be useful in cases where you need to Used in DAX to replace SQL
control rounding. null values.
Linking data in Power BI
Now that we have loaded our data and specified the data type of each column in our datasets, we can now
move on to linking our datasets together.

Power BI Relationships
When we import multiple tables, chances are we will do
some analysis using data from these tables. Relationships
between tables allow the accurate calculation of results
and display the correct information in reports. Power BI
allows us to define relationships between tables, which
enabling us to define visuals which span across multiple
tables and create linked visuals. .

The resulting linked tables are referred to as a data model


in Power BI.

Note: Relationships in Power BI are similar to relationships


in SQL.
Linking data in Power BI

When we import data into Power BI we obtain tables whose columns can be used to create different
types of visuals, measures, and filters. These are useful for extracting insights from data. Power BI
Desktop contains an auto detect feature which “automatically” creating relationships between tables for
you. However, sometimes you might have to create relationships yourself, or make changes to a
relationship. Let’s see how we can do this in Power BI.

Example:
The deliveries table has player stats and no date column, while the matches table has dates, but no
player stats.

Therefore, if we want to analyse player stats over time, these tables need to be joined.
Linking data in Power BI
As in the relationships between SQL tables, Power BI relationships also have cardinality. Each
model relationship must be defined with a cardinality type, the possible relationship cardinality
options are:

Cardinality Notation Description


One row in table A relates to one row in table
one-to-one 1-1
B.

one-to-many One row in table A relates to multiple rows in


1-*
table B.

many-to-one Multiple rows in table A relate to one row in


*-1
table B.
Multiple rows in table A relate to multiple rows
many-to-many *-*
in table B.

Note: the “1” implies unique entries for a given column and the “*” implies multiple values in the
same column.
Setting up a data model
Now we can move onto linking the deliveries and matches tables.

Entity Relationship Diagrams (ERD) are used to visualise


the relationship of shared data between multiple tables.
Next we look at how we can create an ERD in Power BI.

Currently, your ERD should look like the “Before link” Before link
picture on the right. As you can see there is no link
between the two tables.

We need to link the tables using a unique key from one


table. We can see that both tables have a column related
to the match:

● deliveries: match_id
● matches: id

The id’s in matches are unique. We can create a


relationship between the deliveries: match_id and
matches: id.
Setting up a data model
Now we can move onto linking the deliveries and matches tables.

1
Creating a relationship in Power BI 3

1. Navigate and select “Manage Relationships”


on the Home tab.
2. Select “New”.
3. In the “create relationship” dialog box, in the
first table drop-down list, select the column
4
you want to use in the relationship and do
the same for the second table.
4. By default Power BI desktop configures the 5
options: Cardinality (direction), Cross filter
direction. You can change these settings if
necessary.
5. Select “OK”.
6. Your relationship is now active. Complete the
process by selecting “Close.”
6
2
Setting up a data model
Now we can move onto linking the deliveries and matches tables.

Before link
After linking the tables your ERD should look like
the “After link” picture on the right.

To edit a relationship you can do the following:

1. Select Manage Relationship on the Home


tab.
2. In the Manage relationships dialog box,
select the relationship, then select “Edit.”
After link
Conclusion

In this train we learnt how to load data into a Power BI project in preparation
for downstream tasks such as dynamic visual creation. An important
component of such loading surrounds the linking of multiple tables to form an
ERD, allowing analysis to be performed across multiple tables. Lastly, we
observed the various data types supported in PBI, and how these types can be
configured when loading data.

We encourage you to import data with disparate data types in Power BI and
create your very own ERD.
Appendix
Links to additional resources to help with the understanding of concepts presented in the train

● Data types in Power BI

● Query overview in Power BI

● Import Excel workbooks in


Power BI

You might also like