Shaping tables

Data Modeling in Power BI

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

normalization

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 splitting
    2. Column extraction
    3. Query merging
    4. Query appending
  • 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

column splitting

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 fit together
  • Result: keep distinct rows, shrinking total data model size and reducing redundancy

column extraction

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

query appending

Data Modeling in Power BI

4. Query merging

  • Join together two existing tables based on values from one or more columns$^1$
  • Types of joins:
    • Inner join
    • Left outer join
    • Right outer join
    • Full outer join

query merging and types of joins

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!

Data Modeling in Power BI

Preparing Video For Download...