DEV Community: Timothy Atinuke

CONFIGURING SEMANTIC MODEL IN POWER BI

Timothy Atinuke — Sun, 31 May 2026 03:01:03 +0000

INTRODUCTION

Configuring a Power BI semantic model involves refining data structures, creating relationships, and setting up calculations. Semantic model is the last stop in the data pipeline before reports and dashboards are built. It is the end product of the raw data that has been extracted, transformed, loaded, modeled, built relationship, and written calculation. The Semantic model consist of Data connections to one or more data sources,Transformations that clean and prepare the data for reporting, Defined calculations and metrics based on business rules to ensure consistent reports and Defined relationships between tables.

Key words to note in Semantic Modelling are;

1. Fact table and Dimension table:
The Fact table records the quantitative and numerical data. It is where every single details are recorded. The Dimension table act as the descriptive companion to the fact table, containing the attributes or characteristics that provide context to the data.

2. Primary and Foreign Key:
Primary Keys are unique identifier assigned to a specific record with a database table ensuring that no two rows are identical or repeated. foreign Keys are columns or group of columns in one table that provides a link between data in two tables by referencing the primary key of another.

3. Star Schema
Star Schema is a data modeling technique where a central fact table is surrounded by several dimension tables that provide descriptive content.

4. Cardinality
Cardinality defines the kind of relationship between two tables. They are;

One to Many (1.*)
Many to one (*.1)
One to One (1.1)
Many to Many (.) The cardinality of a relationship is described by the "one" (1) or "many" (*) icons located at the ends of the relationship line.

5. Cross Filter Direction
The direction determine how filters propagate. Possible cross filter options are dependent on the relationship cardinality type.

One to Many - Single or Both sides
One to One - Both sides
Many to Many - Single to either table or both The cross filter direction of a relationship is described by the arrows located in the middle of the relationship line.

6. Active and Inactive Relationship
An Active relationship is the primary relationship that is used by Power BI to calculate and filter data automatically.
An inactive relationship is a secondary relationship activated with the Power BI DAX function temporarily whenever there is a need to perform a specific calculation.
An active relationship is a solid line; an inactive relationship is a dotted line.

7. Disconnected Table
Disconnected Table is a table that doesn't need a relationship to another model table and are useful in what-if scenario or field parameters.

ARCHITECTURE OVERVIEW

In this blog, you learn how to:

Create model relationships.
Configure table and column properties.
Create hierarchies.
Create quick measures.
Configure a many-to-many relationship

A. Create Model Relationships
Data modeling is the process of organizing data into tables and creating relationships. The data model is a visual and structural blueprint that defines how data is stored, organized and related within a database.

STEPS

In Power BI Desktop, to view all table fields, in the Data pane, right-click an empty area, and then select Expand All.
To create a table visual, in the Data pane, from inside the Product table, check the Category field.
To add another column to the table, in the Data pane, check the Sales | Sales field.
Notice that the table visual lists four product categories, and that the sales value is the same for each, and the same for the total. This is because there isn't a model relationship between these tables.
To switch to the model designer, at the left select the Model view icon.
On the Home ribbon, select Manage Relationships.
In the Manage Relationships window, notice that no relationships are yet defined.
To create a relationship, select + New relationship.
To configure a relationship from Product table to Sales table, in the From table dropdown list, select the Product table, and in the To table dropdown list, select the Sales table.
The following properties were automatically configured:

ProductKey columns in each table are selected.
Cardinality type is One To Many (1:*), which was automatically detected.
Cross Filter Direction type is Single.
Make This Relationship Active is checked. 11.Select Save, notice in the Manage Relationships window that the new relationship is listed, and then select Close. 12.In the model diagram, there's now a connector between the two tables (between the Product and Sales table which is connected by the ProductKey column). 13.Switch to Report view, and then notice that the table visual updated to display different values for each product category.

B. Create additional relationships
New relationships in Power BI are also created through the drag and drop columns.

STEPS

Firstly, switch to Model view.
From the Reseller table, drag the ResellerKey column onto the ResellerKey column of the Sales table.
In the New relationship window, review the configuration, and then select Save.
Additional model relationships are created using the following; Region table to Sales table using SalesTerritoryKey columns as the common column for both tables. Salesperson table to Sales table, using the EmployeeKey columns as common key for both tables.
In the model view diagram, tables are arranged so that the Sales table is positioned in the center of the diagram, and the related tables are arranged about it. (Disconnected tables are positioned to the side).
Save the Power BI Desktop file.

C. Configure the Product table
In this task, you'll configure the Product table with a hierarchy and display folder.

STEPS

Switch to Model view.
In the Data pane, if necessary, expand the Product table to reveal all fields.
In the Product table, right-click the Category column, and the select Create hierarchy.
In the Properties pane, in the Name box, replace the value with Products.
To add levels to the hierarchy, in the Hierarchy dropdown list, select Subcategory and then select Product, and then select Apply Level Changes.
In the Data pane, notice the Products hierarchy. To reveal the hierarchy levels, expand it.
To organize columns into a display folder, in the Data pane, first select the Background Color Format column.
While pressing the Ctrl key, select the Font Color Format column.
In the Properties pane, in the Display Folder box, enter Formatting. 10.In the Data pane, notice that the two columns are now inside a folder.

D. Configure the Region table
This task configures the Region table with a hierarchy and updated categories.

STEPS

In the Region table, create a hierarchy named Regions, with the three levels: Group, Country and Region.
Select the Country column (not the Country hierarchy level).
In the Properties pane, expand the Advanced section (located at the bottom of the pane), and then in the Data Category dropdown list, select Country/Region.

E. Configure the Reseller table
This task configures the Reseller table to add a hierarchy and update data categories.

STEPS

In the Reseller table, create a hierarchy named Resellers, with the two levels, Business Type and Reseller
Create a second hierarchy named Geography, with the following four levels: Country-Region, State-Province, City, and Reseller
Set the data category for the following columns (not within the hierarchy):

Country-Region to Country/Region
State-Province to State or Province
City to City

F. Configure the Sales table
This task configure the Sales table with updated descriptions, formatting, and summarization.

STEPS

In the Sales table, select the Cost column.
In the Properties pane, in the Description box, enter: Based on standard cost
Select the Quantity column.
In the Properties pane, from inside the Formatting section, set the Thousands Separator property to Yes.
Select the Unit Price column.
In the Properties pane, from inside the Formatting section, set the Decimal Places property to 2.
In the Advanced group (you may need to scroll down to locate it), in the Summarize By dropdown list, select Average. By default, numeric columns will summarize by summing values together. This default behavior isn't suitable for a column like Unit Price, which represents a rate. Setting the default summarization to average will produce a meaningful result.

G. Bulk update properties
This task updates multiple columns using single bulk updates. This approach is used to hide columns, and format column values.

STEPS

In the Data pane (or model diagram), select the ProductKey column in the Product table .
While pressing the Ctrl key, select the following 13 columns SalesTerritoryKey (Region Table), ResellerKey (Reseller), EmployeeKey, ProductKey, ResellerKey, SalesOrderNumber and SalesTerritoryKey (Sales Table), EmployeeID, EmployeeKey, UPN (Salesperson Table), EmployeeKey, SalesTerritoryKey (SalespersonRegion), and EmployeeID (Targets).
In the Properties pane, set the Is Hidden property to Yes.

The columns were hidden because they're either used by relationships or will be used in row-level security configuration or calculation logic.
Multi-select the following three columns: Standard Cost (Product Table), Cost and Sales (Sales Table).
In the Properties pane, from inside the Formatting section, set the Decimal Places property to 0 (zero).

H. Explore the model interface

STEPS

This task explains how to switch to Report view, review the data model interface, and configure the auto date/time setting.

Switch to Report view.
In the Data pane, notice the following:

Columns, hierarchies and their levels are fields, which can be used to configure report visuals.
Only fields relevant to report authoring are visible.
The SalespersonRegion table isn't visible—because all of its fields are hidden.
Spatial fields in the Region and Reseller table are adorned with a spatial icon.
Fields adorned with the sigma symbol (Ʃ) will summarize, by default.
A tooltip appears when hovering the cursor over the Sales (Cost field).
Expand the Sales (OrderDate field), which reveals a Date Hierarchy. They were created automatically as a default setting which commences on January 1 of each year which is not in syn with the dataset used in this analysis, whose financial year commenced on July 1.
To turn off the auto date/time setting, navigate to File Open the Options and Settings and Options.

In the Options window, on the Current File section, navigate to Data Load > Time Intelligence, and uncheck Auto Date/Time.
In the Data pane, notice that the date hierarchies are no longer available.

I. Create quick measures

STEPS

This task creates two quick measures to calculate profit and profit margin. A quick measure creates the calculation formula for you. They're easy and fast to create for simple and common calculations.

In the Data pane, right-click the Sales table, and then select New Quick Measure.
In the Quick Measure pane, in the Select a Calculation dropdown list. From inside the Mathematical Operations group, select Subtraction.
From the Data pane, drag the Sales (Sales field) into the Base Value well.
Drag the Sales (Cost field) into the Value to Subtract box.
Select Add.
In the Data pane, inside the Sales table, notice that new measure. Measures are indicated by the calculator icon.
To rename the measure, right-click it, select Rename, then rename as Profit. Tip: To rename a field, you can also double-click it, or select it and press F2.
In the Sales table, add a second quick measure, based on the following requirements:
Use the Division mathematical operation.
Set the Numerator to the Sales (Profit field).
Set the Denominator to Sales (Sales field).
Rename the measure as Profit Margin.
Ensure the Profit Margin measure is selected, and then on the Measure Tools contextual ribbon, set the format to Percentage, with two decimal places.
To test the two measures, first select the existing table visual on the page.
In the Data pane, check the Profit and Profit Margin measures to add them to the table.
Select and drag the right guide to widen the table visual.
Verify that the measures produce reasonable results that are correctly formatted.

J. Create a many-to-many relationship

STEPS

This task creates a many-to-many relationship between the Salesperson table and the Sales table.

In Report view, select a blank area of the report page.
To create a new table visual, in the Data pane, check the following two fields: Salesperson (Salesperson) and Sales (Sales).
Notice that Michael Blythe has generated almost 9 million dollars of sales.
Switch to Model view, then drag the SalespersonRegion table to position it between the Region and Salesperson tables.
Use the drag-and-drop technique to create the following two model relationships:
Salesperson | EmployeeKey to SalespersonRegion | EmployeeKey
Region | SalesTerritoryKey to SalespersonRegion | SalesTerritoryKey The SalespersonRegion table can be considered to be a bridging table.
Switch to Report view, and then notice that the visual hasn't updated the sales result for Michael Blythe hasn't changed.
Switch back to Model view, and then follow the relationship filter directions (arrowhead) from the Salesperson table. Note that the Salesperson table filters the Sales table. It also filters the SalespersonRegion table, but it doesn't continue by propagating filters to the Region table (the arrowhead is pointing the wrong direction).
To edit the relationship between the Region and SalespersonRegion tables, double-click the relationship.
In the Edit Relationship window, in the Cross Filter Direction dropdown list, select Both. 10.Check the Apply Security Filter in Both Directions checkbox. 11.Select Save. 12.Notice that the relationship has a double arrowhead now. 13.Switch to Report view, and then notice that the sales values have still not changed. 14.Switch to Model view.
To force filter propagation via the bridging table, edit (double-click) the relationship between the Salesperson and Sales tables. 16.In the Edit Relationship window, uncheck the Make This Relationship Active checkbox. 17.Select Save. Filter propagation will now follow the only active path. 18.In the model diagram, notice that the inactive relationship is represented by a dotted line. 19.Switch to Report view, and then notice that the sales for Michael Blythe are now nearly 22 million dollars. 20.Notice also, that the sales for each salesperson—if added—would exceed the table total. 21.Switch to Model view, and then in the model diagram, select the Salesperson table. 22.In the Properties pane, in the Name box, replace the text with Salesperson (Performance). The renamed table now reflects its purpose: it's used to report and analyze the performance of salespeople based on the sales of their assigned sales regions.

K. Relate the Targets table

STEPS

This task creates a relationship to the Targets table.

Create a relationship from the Salesperson (Performance) (EmployeeID column) and the Targets (EmployeeID column).
In Report view, add the Targets (Target field) to the table visual.
Resize the table visual so all columns are visible.

Clean, Transform, and Load Data in Power BI: A Practical Guide to Data Preparation

Timothy Atinuke — Tue, 28 Apr 2026 03:56:17 +0000

INTRODUCTION

Data cleaning is the process of identifying and correcting errors, anomalies and inconsistencies in raw data sets to improve the quality of the data and get it ready for advanced analysis and modeling. In today’s data-driven world, raw data is often messy and rarely ready for analysis. The real value of a data analyst lies not just in collecting data, but in their ability to prepare it for meaningful insights. Data cleaning and transformation is essential because it organize tables, remove duplicates, simplify complicated columns through merging or appending, remove duplicates and null values, improve result accuracy for better and informed decision making. This is where ETL (Extract, Transform, Load) becomes essential. Power Query Editor in Power BI Desktop allows you to shape (transform) your imported data. Power Query Editor only modifies a view of your data, so you can be confident that your original data source remains unchanged.
Using Microsoft Power BI, analysts can efficiently clean, transform, and load data into a structured model ready for visualization and reporting.
This guide walks you through the step-by-step practical process of preparing data in Power BI from cleaning messy datasets to transforming the data into analysis-ready format and finally loading it into the data model for modeling and visualization.

ARCHITECTURE OVERVIEW

Power BI’s data preparation workflow through the Power Query Editor is where:
• Data Cleaning occurs
• Data Transformations are performed
• Data Loading for analysis is applied

Each step performed is recorded, making your workflow reproducible and easy to modify.

Working with Power Query Editor
The Power Query Editor is the central workspace for data transformation.
Steps:
• Access it via Transform Data in Power BI Desktop

• Each transformation step is recorded in the Applied Steps pane

• Changes are repeatable and refresh automatically with new data
Key advantage: It enables a no-code/low-code approach while still supporting advanced transformations.

1. Promoting Headers
Often, datasets load with the first row as data instead of column names.
To promote header,
Steps:

Go to Home Column
Use “Use First Row as Headers”
Ensure columns are properly labeled for easier analysis

2. Creating Index Columns
Index columns help to assign unique integer to each row in a table for easy row identification, sorting and modeling.

Steps:

Go to Add Column → Index Column
Choose: From 0, From 1 or Custom increment (which define a starting value and increment, e.g., 3, 6, 9…)

3. Conditional Columns and Logic
Conditional columns is used in the creation of new columns based on rule (IF-THEN-ELSE logic) applied to the existing columns

Steps:

Navigate to Add Column → Conditional Column
Define logic like:
If Salary = 50,000 → “Full Employee”
Else → “Contract Worker”

4. Column from Examples
It automatically creates new columns by providing one or more example values from existing columns in the table.

Steps:

Go to Add Column → Column from Examples
Provide sample outputs
Power BI automatically detects the transformation logic.

5. Unpivot and Pivot Columns
These operations reorganize dataset making dataset more flexible, consistent and compatible for analysis. Pivot Columns: turn long data (category values) from rows into wide format (separate columns). Unpivot Columns: Converts wide data (columns) into long format (rows).

Steps:

Go to Home → Transform
Select column to unpivot/pivot
Click Pivot column to convert
Chose value column (e.g., Sales) to populate the pivoted data
Click Ok
To Unpivot, click unpivot and unpivot columns
Press Ok

6. Grouping and Aggregation
Grouping helps summarize data by primarily using the “Group By”.
Steps:

Go to Transform → Group By
Apply aggregations like: Sum, Count, Average, Min/Max Example:
Total sales by region
Average revenue per customer

7. Merge and Append Queries
Used for combining datasets:
Merge Queries (Join) extract a particular column from one table to join another table through the use of a common key. E.g. merging customer and transaction tables.
Append Queries (Union) concatenate rows from two tables with the same structure into a single table. E.g. appending monthly datasets.

Merging increases the number of columns in the table while Appending increases the number of rows in the table.

Step: To Merge

Open Power query editor through the transform data
Select the table to merge from the left pane (e.g., CodeSphere Hub Sales 2019)
Click on the Home Tab and select the Merge Queries
Choose “Merge Queries” to merge directly into selected table or “Merge Queries as New” to create a new merged table
Select the data table from the first drop down and select the common column (e.g., Product_Key)
Select the second table from the second drop down and chose the matching column (I.e. Product_Key)
Click ok

Step: To Append

Open Power query editor through the transform data
Select the table to append (e.g., CodeSphere Hub Sales 2019)
Click on the Home Tab and select the Append Queries
Choose “Append Queries” to append directly into selected table or “Append Queries as New” to create a new appended table
Select the first query from the first drop down selection (e.g., CodeSphere Hub Sales 2019)
Select the second query from the second drop down selection (e.g., CodeSphere Hub Sales 2020)
Click ok

8. Date and Time Transformations
It is essential for creating time-intelligence calculations like, year-to-date, same-period-last-year, extracting year, month and day, calculating durations, date differences and standardizing date format for time series analysis and trend reporting.
Step:

Open Power query editor
Navigate to Transform → Date or Navigate to Transform → Time to extract values (Day, week, month, quarter, year).

To standardize date format, Navigate to Data Type, Select Date/Time, Date or Time to ensure accurate modeling.

Using DAX functions like DATEDIF to calculate differences

9. Adding Prefix and Suffix Using Power Query
You can modify text fields by adding prefixes or suffixes.
Steps:

Click on Transform data to open Power Query Editor
Select the column to edit
Navigate to the Transform Tab
Click Format
Add Prefix or Suffix
Enter the desired text/character in the dialog box (e.g. add “NGN” before currency values)
Also enter desired text/character for suffix and Click OK
Or use Custom Column with formulas

10. Data Profiling Techniques
Power Query includes built-in data profiling tools for understanding the structure, quality and distribution of data before loading into the model. These tools help quickly assess data health and identify issues.
The 3 concepts of data profiling techniques in power BI are;

Column Profile: which shows the entire statistics of a selected column and it shows statistics like Count, Error, Empty, Distinct Unique, Not Available Number (NAN), Min, Max, Zero, Avg, Standard Deviation, Even and Odd.
Column Distribution: shows a small histogram under the column header that visualize the frequency and distribution of values. It explicitly list out the number of distinct values and Unique values in a selected column
Column Quality: displays valid data, empty values and percentage of errors

Steps:

Click on Transform data to open Power Query Editor
Select the column to profile
Navigate to the View Tab
Enable the option to preview by checking the box and unchecking others (e.g. check the box to preview column quality)
Column Profile
Column Distribution
Column Quality

11. Handling Missing or Null Values
Missing data can distort analysis if not handled properly.

Steps:

First, identify missing data through the column quality to see the percentage of valid, error and empty (null) values in every column
Click the filter arrow on the column header
Select null (or blank) to view only rows with missing data
To replace manually,
- Right click a column
- Select Replace Values
- Type Blank in the “Value to Find”
- Enter replacement (e.g., 0 or Unknown) in the “Replace With”
To replace null and empty value by filling down,
- Select the column Go to Transform Tab
- Click Fill and select Down
- It copies the last non-null value into the subsequent null cells until it hits a new value
- It is also applicable if the “summary” or “header” values us located below the missing data rows.
To remove Missing Data
- Select column
- Click on filter arrow and chose remove Empty.

CONCLUSION

Data preparation is the most critical step in any analytics workflow. Power BI’s Power Query Editor provides a robust and flexible environment to clean, transform, and load data efficiently.
By mastering these techniques from basic cleaning to advanced transformations you position yourself to build accurate models, insightful dashboards, and impactful data stories.
The difference between average and exceptional analysis often lies not in visualization but in how well the data was prepared.

Getting Data from Multiple Sources in PowerBI: A Practical Guide to Modern Data Integration

Timothy Atinuke — Sun, 05 Apr 2026 18:15:41 +0000

INTRODUCTION

According to Microsoft, Power BI is a complete reporting solution that offers data preparation, data visualization, distribution, and management through development tools and an online platform. Power BI can scale from simple reports using a single data source to reports requiring complex data modeling and consistent themes. Use Power BI to create visually stunning, interactive reports to serve as the analytics and decision engine behind group projects, divisions, or entire organizations.
The foundation of every successful Power BI report is reliable data ingestion. Before a report can be successfully created, ability to extract data from various data sources is the first crucial step to building an effective report. Interacting with SQL Server is different from Excel, so learning the nuances of how data connection from different sources works is important in order to be able to use other PowerBI tools for effective decision making.
In most real-world business contexts, data is typically spread across multiple sources rather than confined to one. A data analyst may need to integrate data from Excel files, CSVs, SQL Server databases, PDFs, JSON APIs, and SharePoint folders into a unified report. Power BI is well-equipped for this task, offering powerful tools like Get Data and Power Query to efficiently connect, combine, and transform data from various sources. This guide explores how Power BI enables multi-source data integration and provides a step-by-step approach to implementing it effectively.
In this guide, you will learn how to:
• Connect Power BI to multiple data sources efficiently
• Use Power Query to preview and explore your data
• Detect and resolve data quality issues early
• Build a strong foundation for accurate data modeling and reporting.

Architecture Overview

At a high level, Power BI follows a layered architecture which consists of:
• Power BI Desktop as the reporting and modeling tool
• Multiple data sources, including:
o Excel and Text/CSV files
o SQL Server databases
o JSON and PDF files
o SharePoint folders
All data flows into Power BI through Power Query, where it is reviewed and prepared before loading into the data model.
Connecting Data from Multiple Sources
Power BI allows you to connect to a wide range of data sources. Below are step-by-step guides for each major source.

Step 1: Connecting to Excel

Open Power BI Desktop
Navigate to Home → Get Data → Excel
Browse and select your Excel file
In the Navigator window, select the required sheets or tables
Click Load (to import directly) or Transform Data (to clean first)

Step 2: Connecting to Text/CSV Files

Open Power BI Desktop
Navigate to Home → Get Data → Text/CSV
Browse and select the CSV file (e.g., MultiTimeline.csv)
Preview the dataset in the dialog window
Click Load or Transform Data

Step 3: Connecting to PDF

Open Power BI Desktop
Navigate to Home → Get Data → PDF
Select the PDF file
Wait for Power BI to detect available tables
Select the desired table(s)
Click Load or Transform Data

Step 4: Connecting to JSON

Open Power BI Desktop
Navigate to Home → Get Data → JSON
Select the JSON file or input API endpoint
Load the data into Power Query
Expand nested fields to structure the data properly
Click Close & Apply

Step 5: Connecting to SharePoint Folder

Open Power BI Desktop
Navigate to Home → Get Data → SharePoint Folder
Enter the SharePoint site URL
Click OK and authenticate if required
Select files from the folder
Click Combine & Transform Data

Step 6: Connecting to MySQL Database

Open Power BI Desktop
Navigate to Home → Get Data → MySQL Database
Enter the server name and database
Provide authentication credentials and click connect
Select the required tables
Click Load or Transform Data

Step 7: Connecting to SQL Server

Open Power BI Desktop
Navigate to Home → Get Data → SQL Server
Enter the server name (e.g., localhost)
Leave the database field blank (or specify one if needed)
Click OK
Select authentication method (e.g., Windows credentials)
In the Navigator pane, expand the database (e.g., AdventureWorksDW2020)
Select required tables such as: o DimEmployee o DimProduct o DimAccount
Click Transform Data to open Power Query Editor

Step 8: Connecting to Web Data

Open Power BI Desktop
Navigate to Home → Get Data → Web
Enter the URL of the web page or API
Click OK
Select the data table or structure detected
Click Load or Transform Data

Step 9: Connecting to Azure Analysis Services

Open Power BI Desktop
Navigate to Home → Get Data → Azure → Azure Analysis Services
Enter the server name
Select the database/model
Choose connection mode (Live connection recommended)
Click Connect

Conclusion

Integrating data from multiple sources in Microsoft Power BI is a foundational skill for modern data analysts. By understanding the architecture and following a structured approach, you can transform fragmented datasets into cohesive, insight-driven reports. Ultimately, great analytics begins with great data and great data begins with how well you connect, prepare, understand and use it to make business decisions.
Mastering tools like Power Query and applying best practices in data modeling will significantly enhance the quality and performance of your analytics solutions.