Data modeling and
table properties
D ATA M O D E L I N G I N P O W E R B I
Maarten Van den Broeck
Content Developer at DataCamp
What is a data model?
Conceptual view of data elements
Typically a visual representation
Data models include:
Tables
Columns
Relationships between tables
Data types
Keys
DATA MODELING IN POWER BI
Data modeling
The process of creating a data model
Why model data?
Data ≠ perfect
Reshape data for analysis
Compress data usage
Easier to understand model
DATA MODELING IN POWER BI
Data modeling in Power BI & Power Query
Power Query is the data preparation tool of
di erent Microso products, including
Power BI
Main goals:
Manage queries
Data modeling
Data modeling: 80% in Power Query, 20%
in Power BI
DATA MODELING IN POWER BI
Columns and row management
Operation Example
Keep or remove speci c columns Remove empty column
Keep or remove speci c rows Keep top row as header
Split a single column in multiple columns DD/MM/YYYY column split in DD, MM,
YYYY columns
Summarize/group rows in a table by the
contents of a column Sum or median of all rows
DATA MODELING IN POWER BI
Data types
Choosing the right data type is essential:
Constrain data to a speci c shape
Optimize storage
Enable speci c functionality
Power Query infers data type on rst few
hundred rows
DATA MODELING IN POWER BI
Rounding
Power Query Power BI
Actually changes the data, not just Changes how the data appears, not how
forma ing it's stored
Typically not the right answer Generally a be er answer than rounding in
Power BI
DATA MODELING IN POWER BI
The dataset
United States Census Bureau survey data of manufacturers
Summary statistics for manufacturing rms
North American Industry Classi cation System (NAICS)
DATA MODELING IN POWER BI
Let's practice!
D ATA M O D E L I N G I N P O W E R B I
Load and transform
data
D ATA M O D E L I N G I N P O W E R B I
Maarten Van den Broeck
Content Developer at DataCamp
Let's practice!
D ATA M O D E L I N G I N P O W E R B I
Shaping tables
D ATA M O D E L I N G I N P O W E R B I
Maarten Van den Broeck
Content Developer at DataCamp
Database normalization
A set of logical rules and processes to
follow for data modeling
Organizing a database
Goals of normalization
Remove redundant data
Achieve a design which is a good
representation of the real world
Tables are connected through relationships
in Power BI
DATA MODELING IN POWER BI
Data shaping in Power Query
Power Query includes several data shaping operations to get closer to a normalized data
model.
Key techniques:
1. Column spli ing
2. Column extraction
3. Query appending
4. Query merging
There are additional techniques as well!
DATA MODELING IN POWER BI
1. Column splitting
Break out one column into multiple columns
Split criteria
Delimiter
Number of characters
Position in string
Lower vs. upper casing
Digit vs. non-digit
DATA MODELING IN POWER BI
2. Column extraction
Take columns from one table and break
them out into another table
Keep a key on the original table to know
which values t together
Result: keep distinct rows, shrinking total
data model size and reducing redundancy
DATA MODELING IN POWER BI
3. Query appending
Combine contents of two or more tables
into a single table
Match rows based on column names,
adding NULL for missing columns
Equivalent to a UNION ALL statement in
SQL
DATA MODELING IN POWER BI
4. Query merging
Join together two existing tables based on
values from one or more columns1
Types of joins:
Inner join
Le outer join
Right outer join
Full outer join
1 This won't be covered in the exercises; check other DataCamp courses on joining tables for more detail
DATA MODELING IN POWER BI
Let's practice!
D ATA M O D E L I N G I N P O W E R B I
Merging and
appending queries
D ATA M O D E L I N G I N P O W E R B I
Maarten Van den Broeck
Content Developer at DataCamp
Let's practice!
D ATA M O D E L I N G I N P O W E R B I
