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 datasets, 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.

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: Once the transformation is done make sure all the filters applied are cleared. Only filtered rows will get loaded if any filters are applied.

Data Preparation & Cleaning

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

For demonstration purposes in this learning series, null values in the Age, Height, and Weight columns were replaced with 0 to simplify later visualizations and calculations. In real-world projects, you should evaluate the business requirements before replacing missing values.

To replace Null values as NA or 0

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. Power BI automatically creates a Semantic Model, which serves as the foundation for report development and analysis.

Once the data is loaded, Power BI creates a Semantic Model that organizes tables, relationships, and calculations. 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.

Power BI automatically detected a many-to-one relationship between the Olympic Events table and the NOC Region table based on the NOC column. You can review or modify this relationship in Model view if required.

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 values, duplicate records, unexpected values, and inconsistencies, making it easier to clean and validate datasets.

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. 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.