Lesson 7 - Power BI Semantic Model to build great visuals

Before creating meaningful visualizations in Power BI, it is important to prepare and organize your data. In this lesson, we'll import multiple data files and tables, clean and transform the data using Power Query Editor, build relationships, and create a semantic model that will be used throughout this Power BI learning series.

🚀 Learn Power BI in 5 Minutes

Skip the long read.
See the key Power BI concepts in one easy-to-follow infographic.

View Infographic →

Dataset Used:

We use the "120 Years of Olympic History: Athletes and Results" dataset from Kaggle as the primary dataset. To support the lessons in this series, additional supporting datasets, lookup tables, and custom dimension tables have been included to demonstrate Power BI visuals, semantic modeling, and advanced relationship scenarios.

Context:

The "120 Years of Olympic History: Athletes and Results" dataset from Kaggle contains historical Olympic data from Athens 1896 to Rio 2016. The original dataset includes two files: Olympic Events, containing 268,768 records with athlete participation details, and NOC Regions, containing 230 records that map National Olympic Committee (NOC) codes to countries and regions.

In the original Olympic Games, the Summer and Winter Olympics were held in the same year until 1992. From 1994 onwards, the Summer and Winter Games each continued on their own four-year cycle, staggered two years apart (for example, Winter 1994, Summer 1996, Winter 1998, and so on).

Throughout this learning series, we will use the enhanced dataset to explore various Power BI concepts, including data modeling, semantic models, geographical analysis, and report visualizations. We will analyze different aspects of the Olympic Games, such as country performance, athlete performance, medal trends, geographical insights, and the evolution of women's participation over time.

Field Description:

Olympic Events – File 1

• ID – Unique identification number for each athlete (Integer)
• Name – Name of the athlete (Text)
• Gender – Gender of the athlete (M/F) (Text)
• Age – Age of the athlete (Integer)
• Height – Height of the athlete in centimeters (Integer)
• Weight – Weight of the athlete in kilograms (Decimal Number)
• Team – Name of the team the athlete represented (Text)
• NOC – Three-letter code representing the National Olympic Committee (Text)
• Games – Olympic Games edition (Year and Season) (Text)
• Year – Year in which the Olympic Games were held (Integer)
• Season – Summer or Winter (Text)
• City – Host city of the Olympic Games (Text)
• Sport – Sport category (Text)
• Event – Specific event within the sport (Text)
• Medal – Medal won (Gold, Silver, Bronze, or No Medal) (Text)
• GamesOpeningDate – Official opening ceremony date of the Olympic Games (Date)

NOC Region – File 2

• NOC – Three-letter code representing the National Olympic Committee (Text)
• Region – Country or region name (Text)
• Notes – Additional information (Text)
• Continent – Name of the continent (Text)

Medal Target – File 3

This is a custom dataset created for this learning series to demonstrate Gauge visuals and target-based analysis.

• MedalsWon – Medal count by region (Integer)
• Region – Name of the region (Text)
• Target – Target medal count for each region (Integer)

DimAthlete – File 4

A unique list of athletes used to demonstrate many-to-many relationships and dimensional modeling in later lessons.

• AthleteID – Unique athlete identifier (Integer)
• Name – Athlete name (Text)
• Gender – Gender (M/F) (Text)

DimEvent – File 5

A unique list of Olympic events used together with DimAthlete to demonstrate many-to-many relationships.

• EventID – Unique event identifier (Text)
• Sport – Sport category (Text)
• Event – Specific event name (Text)

AthleteEventBridge – File 6

A bridge (junction) table that links athletes to every event they participated in. This table resolves the many-to-many relationship between DimAthlete and DimEvent.

• AthleteID – Links to DimAthlete (Integer)
• EventID – Links to DimEvent (Text)

Importing Dataset into Power BI

Step 1: Launch Power BI desktop. Import the Data files into Power BI

Step 2: Import the data Source to the Report

Step 3: Once we import the data into power BI, the below dialog box will get opened.

Load: The Load option imports the data into Power BI and creates a semantic model, allowing you to begin building reports and visualizations immediately.

Transform Data: The Transform Data option allows you to shape, clean, and prepare your data before loading it into Power BI. When you connect to a data source, Power BI opens the Power Query Editor, where you can perform a wide range of data transformation tasks, including:

• Renaming columns and tables
• Removing unnecessary columns
• Filtering rows
• Splitting and merging columns
• Changing data types
• Creating custom columns and calculations
• Combining or appending queries
• Performing many other data transformation operations

These transformations help ensure that your data is clean, consistent, and in the correct format for analysis. Once you have finished transforming the data, select Close & Apply to load it into the semantic model.

Every transformation performed in Power Query is automatically recorded as an Applied Step. This makes the entire data preparation process repeatable, easy to maintain, and allows you to modify or remove individual transformation steps at any time without repeating the entire process.

Note: Filters applied in Power Query are also transformation steps. When you select Close & Apply, only the rows that meet those filter conditions are loaded. Before loading the data, review your Applied Steps and remove any filters that were used only temporarily while exploring the data.

Data Preparation & Cleaning

In the above dataset we made some basic transformations using Power Query Editor.

When working with missing values, it is important to understand what the missing value represents before replacing it.

For demonstration purposes in this learning series, some missing values may be replaced to simplify later examples.

However, in real-world solutions, you should avoid automatically replacing missing Age, Height, or Weight values with 0 unless zero has a valid business meaning.

For example, replacing an unknown height with 0 can affect calculations such as Average Height and produce misleading results.

Some common data preparation tasks include:

To replace Null values with NA or another appropriate value

Choose a replacement value based on the meaning and data type of the column.

To remove duplicates

To change Datatypes

To Extract values from the column

Once the required transformations are completed, select Close & Apply to load the transformed data into Power BI Desktop.

Once the data is loaded, Power BI creates a Semantic Model that contains the tables, relationships, calculations, measures, and business logic that reports use for analysis and visualization. A well-designed semantic model improves report performance, simplifies DAX calculations, and makes reports easier to maintain.

You can view the semantic model by selecting Model view from the left navigation pane.

Where possible, structure semantic models using star schema principles. Dimension tables are generally used for filtering and grouping, while fact tables contain the detailed data and numerical values that are summarized and analyzed.

Relationships allow Power BI to connect information stored across different tables.

When multiple tables are loaded, Power BI Desktop can attempt to automatically detect relationships based on matching columns and the values within those columns.

In our example, Power BI can identify a relationship between the Olympic Events table and the NOC Region table using the NOC column.

NOC Region (1) → Olympic Events (*)

The NOC Region table contains the lookup information for each NOC, while the Olympic Events table can contain many athlete participation records for the same NOC.

This is a common one-to-many relationship pattern.

You can further edit relationships or make other semantic model changes from Model view by selecting the relationship line or using the Manage relationships option.

Under the option Cross filter direction, we have two options

•        Single directional filter
•        Bi-directional filter

Single (Single-directional) – In a single-directional relationship, filters flow in only one direction. Filtering one table affects the related table, but not vice versa.

Both (Bi-directional) – In a bi-directional relationship, filters flow in both directions. Filtering either table affects the related table, allowing filters to propagate across the relationship.

Let's create visuals based on the above dataset in further blogs. Get ready!

Download Raw files - Download the sample datasets (ZIP)

Download .pbix files - Download the completed Power BI report (.pbix)

Data Profiling

Power Query includes built-in Data Profiling features that help assess data quality before loading data into the model.

These include:

• Column Quality
• Column Distribution
• Column Profile

These tools help identify null or empty values, errors, duplicate or repeated values, distinct or unique values, unexpected values, and data inconsistencies, making it easier to clean and validate datasets.

By default, Power Query performs column profiling using the first 1,000 rows of the data.

You can change this to profile the entire dataset by selecting the profiling option at the bottom of the Power Query Editor and choosing Column profiling based on entire dataset.

Conclusion

Preparing and organizing your data is the first step toward building meaningful Power BI reports. By importing data, performing transformations in Power Query Editor, creating a well-structured semantic model, and establishing the right relationships, you ensure accurate analysis and better report performance. 

Following good modeling practices such as using appropriate data types, understanding relationship cardinality, applying sensible filter directions, and using star-schema principles where appropriate also makes your semantic model easier to understand and maintain.

With the dataset now ready, you're all set to explore the wide range of Power BI visuals in the upcoming lessons and create interactive, insightful dashboards.

Useful Information

Tags	Power BI
Useful links	Semantic models in Power BI - Power BI | Microsoft Learn
MS Learn Modules	Configure a Semantic Model in Power BI Desktop - Training | Microsoft Learn
Test Your Knowledge	Quiz