DEV Community: Ibrahim Abdulrasaq

Configure a Semantic Model in Power BI: A Beginner-Friendly Step-by-Step Guide

Ibrahim Abdulrasaq — Fri, 02 Oct 2026 06:29:36 +0000

A good Power BI report starts with a good data model.

You can create beautiful charts and dashboards, but if the tables in your model are not properly connected or your fields are poorly configured, your report can produce misleading results.

This is where a semantic model comes in.

A semantic model in Power BI defines how your data is organized and how different tables interact with one another. It includes tables, columns, relationships, hierarchies, measures, and other properties that make your data easier to understand and use when building reports.

In this guide, we will walk through how to configure a semantic model in Power BI Desktop using a practical sales dataset.

By the end of the tutorial, you will know how to:

Create relationships between tables
Understand cardinality and filter direction
Create and configure hierarchies
Organize fields using display folders
Configure data categories
Add descriptions and formatting to columns
Hide technical columns
Configure default summarization
Turn off automatic date/time hierarchies
Create quick measures
Configure a many-to-many relationship
Use a bridging table
Connect a targets table to your model

What Is a Semantic Model in Power BI?

Before we start building, let's understand what we are configuring.

A semantic model is the structure that tells Power BI how your data should be understood and used.

For example, imagine you have these tables:

Product: contains product information
Sales: contains sales transactions
Reseller: contains reseller information
Region: contains sales territory information
Salesperson: contains salesperson information
Targets: contains sales targets

These tables contain related information, but Power BI needs to know how they are connected.

For example:

One product can appear in many sales transactions.

This means the Product table and Sales table can have a one-to-many relationship.

Once relationships are correctly configured, selecting a product category can automatically filter the corresponding sales records.

That is the foundation of a useful Power BI semantic model.

Getting Started

For this tutorial, we will use the Microsoft Power BI learning dataset.

Download the starter ZIP file and extract it to your computer.

Inside the extracted folder, open:

03-Starter-Sales Analysis.pbix

When Power BI Desktop opens the file, you may see a sign-in window. If you do, select Cancel.

If Power BI displays other informational messages, close them. If prompted to apply changes later, select Apply Later.

Once the file opens, you are ready to begin.

Step 1: Inspect the Existing Data

Before creating relationships, it is useful to understand the tables available in the model.

In Power BI Desktop:

Go to the Data pane.
Right-click an empty area inside the pane.
Select Expand All.

This allows you to see the columns available in each table.

Some of the important tables in this model include:

Product
Sales
Reseller
Region
Salesperson
SalespersonRegion
Targets

The Sales table contains transaction-level information, while the other tables provide additional information that can be used to analyze those transactions.

Step 2: See What Happens Without a Relationship

Let's first see why relationships are important.

In Report view, create a table visual.

From the Product table, select:

Product | Category

Then add:

Sales | Sales

You should see the product categories, but the sales value may be repeated for each category.

Why?

Because Power BI currently doesn't have a relationship connecting the Product table to the Sales table.

The category comes from one table, while sales comes from another. Without a relationship, the filter from Product cannot properly propagate to Sales.

This is one of the most important concepts to understand when working with Power BI data models.

Step 3: Create the Product-to-Sales Relationship

Now let's fix the problem.

Switch to Model view by selecting the Model icon on the left side of Power BI Desktop.

On the Home ribbon, select:

Manage relationships

If there are no relationships, select:

+ New relationship

Configure the relationship as follows:

Setting	Value
From table	Product
From column	ProductKey
To table	Sales
To column	ProductKey
Cardinality	One to many (1:*)
Cross filter direction	Single
Active	Yes

Power BI may automatically detect the matching ProductKey columns.

Understanding the relationship

The relationship is:

Product (1) → Sales (*)

This means one product can appear in many sales records.

The Single cross-filter direction means that filters generally flow from the Product table to the Sales table.

For example:

Product Category → Product → Sales

If a user selects the "Bikes" category, Power BI can now filter the corresponding sales records.

Select Save, then Close.

Return to Report view.

The table should now show different sales values for the different product categories.

That's the relationship doing its job.

Step 4: Understand Relationship Symbols

When you look at the model diagram, Power BI displays useful information on the relationship line.

You may see:

1 and Many

These represent cardinality.

1 = one
* = many

So:

*1 → ** means one-to-many.

You may also see an arrow showing the filter direction.

The relationship line itself can also be:

Solid = active relationship
Dotted = inactive relationship

These visual indicators make it easier to understand the model without opening every relationship.

Step 5: Create Additional Relationships Using Drag and Drop

Power BI provides another easy way to create relationships.

Instead of using Manage relationships, you can drag a column from one table onto its matching column in another table.

In Model view, drag:

Reseller | ResellerKey

onto:

Sales | ResellerKey

Review the relationship and select Save.

Then create these additional relationships:

Region → Sales

Region | SalesTerritoryKey

Sales | SalesTerritoryKey

Salesperson → Sales

Salesperson | EmployeeKey

Sales | EmployeeKey

After creating the relationships, arrange the model so that the Sales table is positioned near the center, with related tables around it.

This makes the model easier to understand visually.

Save the Power BI file.

Step 6: Create a Product Hierarchy

Hierarchies make it easier for report users to navigate data from a higher level to a more detailed level.

For example:

Category → Subcategory → Product

Let's create one.

In Model view, expand the Product table.

Right-click:

Category

Select:

Create hierarchy

Power BI will create a new hierarchy.

In the Properties pane, rename the hierarchy:

Products

Now add two more levels:

Subcategory
Product

Select Apply Level Changes.

You should now have:

Products

Category
- Subcategory
- Product

This hierarchy can later be used for drill-down in report visuals.

Step 7: Organize Columns Using a Display Folder

As your model becomes larger, the Data pane can become difficult to navigate.

Display folders help organize related fields.

In the Product table, select:

Background Color Format

Hold Ctrl and select:

Font Color Format

In the Properties pane, enter:

Formatting

in the Display Folder property.

The two columns will now appear inside a Formatting folder.

Display folders do not change the data itself. They are simply a way of organizing fields and making the model easier for report authors to use.

Step 8: Configure the Region Table

Now let's configure the Region table.

Create a hierarchy called:

Regions

Add these three levels:

Group
Country
Region

Next, select the actual Country column, not the hierarchy level.

In the Properties pane:

Expand Advanced.
Find Data Category.
Select Country/Region.

Why does this matter?

Data categories provide additional information to Power BI about what a field represents.

For example, categorizing a field as Country/Region helps Power BI interpret the field correctly when working with map visuals.

Step 9: Configure the Reseller Table

The Reseller table also needs some useful hierarchies.

Create the Resellers hierarchy

Create a hierarchy called:

Resellers

Add:

Business Type
Reseller

Create the Geography hierarchy

Create another hierarchy called:

Geography

Add:

Country-Region
State-Province
City
Reseller

Now configure the geographic columns.

Set:

Country-Region → Country/Region

State-Province → State or Province

City → City

Remember to select the actual columns when configuring their data categories, rather than the hierarchy levels.

Step 10: Improve the Sales Table

A well-configured semantic model should be easy for other people to understand.

Let's improve the Sales table.

Add a description to Cost

Select:

Sales | Cost

In the Properties pane, find Description and enter:

Based on standard cost

Descriptions are useful because report authors can see additional information when they hover over the field.

Format Quantity

Select:

Sales | Quantity

Under Formatting, set:

Thousands Separator → Yes

This makes large values easier to read.

Format Unit Price

Select:

Sales | Unit Price

Set:

Decimal Places → 2

Then, under Advanced, change:

Summarize By → Average

This is important.

Power BI commonly summarizes numeric columns using Sum by default.

But adding unit prices together usually doesn't make business sense.

For example, if three products have unit prices of:

A total of $60 isn't a meaningful "unit price."

An average of $20 is more useful.

This is why choosing the appropriate default summarization matters.

Step 11: Hide Technical Columns

Not every column needs to be visible to report users.

Some columns exist mainly to support:

Relationships
Calculations
Row-level security
Internal model logic

Select the following columns and set:

Is Hidden → Yes

Product

Product | ProductKey

Region

Region | SalesTerritoryKey

Reseller

Reseller | ResellerKey

Sales

Sales | EmployeeKey
Sales | ProductKey
Sales | ResellerKey
Sales | SalesOrderNumber
Sales | SalesTerritoryKey

Salesperson

Salesperson | EmployeeID
Salesperson | EmployeeKey
Salesperson | UPN

SalespersonRegion

SalespersonRegion | EmployeeKey
SalespersonRegion | SalesTerritoryKey

Targets

Targets | EmployeeID

Hiding these fields doesn't delete them.

They remain available to Power BI for model operations, but they are no longer visible to report authors in the normal Data pane.

This makes the model cleaner and easier to use.

Step 12: Format Financial Columns

Select these three columns:

Product | Standard Cost
Sales | Cost
Sales | Sales

Set:

Decimal Places → 0

This removes unnecessary decimal places and makes the values easier to read.

Step 13: Turn Off Auto Date/Time

Power BI can automatically create date hierarchies for date columns.

For example:

OrderDate

may automatically provide:

Year
Quarter
Month
Day

This can be convenient, but automatic date/time isn't always appropriate for a well-designed model.

For this Adventure Works model, the financial year begins on July 1, while the automatic date hierarchy follows a calendar year beginning on January 1.

A proper date table gives you much more control over this type of business logic.

To turn off Auto Date/Time:

Go to:

File → Options and settings → Options

Under Current File, go to:

Data Load → Time Intelligence

Uncheck:

Auto Date/Time

Select OK.

The automatically generated date hierarchies will disappear.

Step 14: Create a Quick Measure for Profit

Power BI's Quick Measures feature can create common calculations without requiring you to manually write the DAX formula.

Right-click the Sales table and select:

New Quick Measure

In the Quick Measure pane, choose:

Mathematical Operations → Subtraction

Set:

Base Value:

Sales | Sales

Value to Subtract:

Sales | Cost

Select Add.

Power BI creates a measure.

Rename it:

Profit

The calculation is essentially:

Profit = Sales - Cost

The calculator icon beside the field indicates that it is a measure.

Step 15: Create a Profit Margin Measure

Create another Quick Measure.

This time select:

Mathematical Operations → Division

Set:

Numerator:

Sales | Profit

Denominator:

Sales | Sales

Select Add.

Rename the new measure:

Profit Margin

The calculation is essentially:

Profit Margin = Profit ÷ Sales

Select the Profit Margin measure.

On the Measure Tools ribbon, format it as:

Percentage

Set:

Decimal Places → 2

For example, a result of 0.25 will be displayed as:

25.00%

Step 16: Test the Measures

Return to the report page.

Select the existing table visual.

Add:

Profit
Profit Margin

Resize the visual if necessary so all the columns are visible.

Check that the values are reasonable and that the profit margin is displayed as a percentage.

This is a good example of why measures are useful.

Instead of storing another column for every calculation, a measure calculates the result based on the current filter context of the report.

Step 17: Understand the Many-to-Many Challenge

Now we get to a more advanced part of the model.

Create a table visual containing:

Salesperson | Salesperson

and:

Sales | Sales

The visual shows sales generated by each salesperson.

However, there is another business relationship to consider.

A salesperson can belong to multiple sales regions.

At the same time, a sales region can have multiple salespeople.

This creates a many-to-many scenario.

For example:

One salesperson → multiple regions

and:

One region → multiple salespeople

Trying to connect these tables directly can create ambiguity in the model.

This is where a bridge table becomes useful.

Step 18: Use SalespersonRegion as a Bridge Table

The SalespersonRegion table can act as a bridge between Salesperson and Region.

In Model view, position it between the two tables.

Create these relationships:

Salesperson → SalespersonRegion

Salesperson | EmployeeKey

to:

SalespersonRegion | EmployeeKey

Region → SalespersonRegion

Region | SalesTerritoryKey

to:

SalespersonRegion | SalesTerritoryKey

The resulting structure is conceptually:

Salesperson → SalespersonRegion ← Region

The bridge table helps represent the many-to-many assignment between salespeople and regions.

Step 19: Configure Filter Direction

At this point, the report may still not produce the expected sales result.

Why?

Look at the arrows on the relationships.

The filter may travel from Salesperson to SalespersonRegion, but not continue through to Region.

To allow the required filter propagation, edit the relationship between:

Region

and:

SalespersonRegion

Double-click the relationship.

Set:

Cross Filter Direction → Both

Then check:

Apply Security Filter in Both Directions

Select Save.

The relationship will now display double arrowheads.

Step 20: Avoid Ambiguous Filter Paths

There is another issue.

There may now be two possible paths between Salesperson and Sales.

For example:

Salesperson → Sales

and:

Salesperson → SalespersonRegion → Region → Sales

When multiple active paths exist between tables, Power BI can encounter ambiguous filter propagation.

This is generally something you should avoid when designing a production semantic model.

In this lab, we solve the issue by making the direct Salesperson to Sales relationship inactive.

Double-click the relationship between:

Salesperson

and:

Sales

Uncheck:

Make This Relationship Active

Select Save.

The relationship will now appear as a dotted line.

The active filtering path will instead pass through the bridge structure.

Step 21: Rename the Salesperson Table

The Salesperson table is now being used for a specific analytical purpose.

Select the table in Model view.

In the Properties pane, rename it:

Salesperson (Performance)

The new name makes the purpose of the table clearer to anyone working with the model.

Good naming is a small detail, but it becomes increasingly important as Power BI models become more complex.

Step 22: Connect the Targets Table

Finally, let's connect salespeople to their targets.

Create a relationship between:

Salesperson (Performance) | EmployeeID

and:

Targets | EmployeeID

Then return to Report view.

Add:

Targets | Target

to the table visual.

Resize the visual if necessary.

You can now compare sales information with target information.

However, there are two important considerations:

1. Time period

The targets table may contain future target values if no time filter has been applied.

2. Targets are not additive

Adding individual target values together may not always produce a meaningful total.

This is a good example of why understanding the meaning of your data is just as important as understanding Power BI's technical features.

Final Model Checklist

Before considering your semantic model complete, review the following:

Relationships

Product is connected to Sales.
Reseller is connected to Sales.
Region is connected to Sales.
Salesperson is connected to the model.
SalespersonRegion is used as a bridge table.
Targets is connected to the salesperson performance table.
Relationship cardinality and filter directions are appropriate.
Unnecessary ambiguous paths are avoided.

Hierarchies

Products hierarchy
Regions hierarchy
Resellers hierarchy
Geography hierarchy

Data categories

Country fields categorized as Country/Region
State fields categorized as State or Province
City fields categorized as City

Organization

Formatting fields placed in a display folder.
Technical columns hidden.
Tables and fields given meaningful names.
Descriptions added where useful.

Formatting

Quantity uses a thousands separator.
Unit Price uses two decimal places.
Standard Cost, Cost and Sales use zero decimal places.
Profit Margin is formatted as a percentage.

Measures

Profit measure created.
Profit Margin measure created.

Date settings

Auto Date/Time disabled.

Why Semantic Modeling Matters

Configuring a semantic model is not just about connecting tables.

It is about creating a structure that allows Power BI to interpret your data correctly.

A well-designed model helps ensure that:

Filters work as expected.
Calculations return meaningful results.
Report users can find the fields they need.
Technical fields don't clutter the Data pane.
Hierarchies support intuitive drill-down.
Geographic fields work correctly with maps.
Measures are reusable across different visuals.
Complex relationships can be handled more effectively.

In other words, the semantic model sits behind the report and provides the foundation for reliable analysis.

Key Takeaways

If you're just starting with Power BI, focus on these concepts first:

1. Relationships connect your data.

Tables need correctly configured relationships so filters can propagate between them.

2. Cardinality describes how tables relate.

Common relationship types include one-to-many and many-to-many.

3. Filter direction matters.

It determines how filters move between related tables.

4. Hierarchies improve navigation.

They allow users to move from broad categories to more detailed information.

5. Data categories provide additional meaning.

They help Power BI understand fields such as countries, states and cities.

6. Hide technical fields.

Not every field needs to be exposed to report users.

7. Use measures for calculations.

Measures such as Profit and Profit Margin can respond dynamically to the current filter context.

8. Be careful with many-to-many relationships.

Bridge tables can help model many-to-many scenarios, but filter paths must be designed carefully to avoid ambiguity.

9. A clean model makes reporting easier.

The goal isn't simply to make the model work. It should also be understandable and easy for others to use.

Conclusion

A Power BI report is only as reliable as the model behind it.

By correctly configuring relationships, hierarchies, data categories, formatting, measures and filter behavior, you create a semantic model that makes your data easier to analyze and your reports easier to build.

If you're learning Power BI, don't rush straight into creating charts and dashboards. Spend time understanding the model first.

Once the model is properly configured, building meaningful reports becomes much easier.

Happy modeling!

Clean, Transform, and Load Data in Power BI: A Beginner-Friendly Guide

Ibrahim Abdulrasaq — Sun, 25 Jan 2026 10:41:24 +0000

Data is only as powerful as the shape it’s in. Before charts impress and dashboards tell stories, data must be cleaned, structured, and trusted. In Power BI, that transformation happens in Power Query, the engine that turns raw, messy data into meaningful insights.

If you are new to Power BI, one of the most important skills you need to learn is data preparation. No matter how good your visuals look, poor data quality will always lead to poor insights. This is why cleaning and transforming data is a critical step in any Power BI project.

Power Query is the engine in Power BI that allows you to clean, transform, and prepare your data before it is loaded into the data model. With Power Query, you can fix inconsistencies, handle missing values, reshape tables, combine data from different sources, and apply user-friendly naming conventions that make your model easy to understand.

In this beginner-friendly guide, I will walk you step by step through how to clean, transform, and load data in Power BI using a practical, hands-on dataset.

What You Will Learn

By the end of this guide, you will be able to:

Resolve inconsistencies, unexpected values, and data quality issues
Remove duplicate records
Remove or replace null values
Apply user-friendly value replacements
Profile data to understand column quality
Evaluate and transform column data types
Reshape tables using pivot and unpivot operations
Combine and merge queries
Apply clear and user-friendly naming conventions
Edit transformations using the Advanced Editor (M code)
Load a clean and reliable data model into Power BI

Getting Started: Download the Practice Files

To follow along with this guide:

Download the dataset from the link below:

https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/Allfiles/Labs/02-transform-data-power-bi/02-transform-data.zip

Extract the zip file to:

C:\Users\Downloads\02-transform-data

C:\This PC\Downloads\02-transform-data

Open the file 02-Starter-Sales Analysis.pbix

Note: If a sign-in dialog appears, select Cancel. Close any informational windows and select Apply Later if prompted.

Opening Power Query Editor

In Power BI Desktop, go to the Home tab and select Transform Data.

This opens the Power Query Editor, where all data cleaning and transformation tasks are performed.

Let's start with how to configure a table/query

This section will walk you through how to:

Rename query/table
Locate column
Filter
Merge columns
Delimiter Etc.

Configuring the Salesperson Query

Select the DimEmployee query and rename it to Salesperson. Query names become table names in the data model.
Note: Always use clear and meaningful names.

Use Go to Column to locate the SalesPersonFlag column and filter it to TRUE so that only salespeople are included.

Remove all unnecessary columns and keep only:

EmployeeKey
EmployeeNationalIDAlternateKey
FirstName
LastName
Title
EmailAddress

Merge the FirstName and LastName columns using a space as the separator and name the new column Salesperson.

Rename EmployeeNationalIDAlternateKey to EmployeeID and EmailAddress to UPN.

Configuring the SalespersonRegion query

Select the DimEmployeeSalesTerritory query and rename it to SalespersonRegion.

Remove the DimEmployee and DimSalesTerritory columns.

Configuring the Product Query

Select the DimProduct query and rename it to Product.

Filter the FinishedGoodsFlag column to TRUE so that only finished goods are included.

Remove all columns except:

ProductKey
EnglishProductName
StandardCost
Color
DimProductSubcategory

Expand the DimProductSubcategory column and include:

EnglishProductSubcategoryName
DimProductCategory

Note: Ensure the option to use original column names as prefixes is unchecked.

Having followed up to this stage. You should now be able to configure the Reseller, Region, Sales, and Targets queries.

Note: In this context, a query is the same as a table in the data model.

Now, let's move to other aspects of data cleaning and transformation.

Removing Duplicates in Power Query

To remove duplicate records, select the column or columns that should be unique, right-click, and choose Remove Duplicates. Power Query keeps the first occurrence and removes the rest.

Removing or Replacing Null Values

To remove null values, open the column filter and uncheck null.

You can also replace null values. To do this, right-click the column, select Replace Values, and enter an appropriate value to replace the nulls.

A few notes:

Power Query distinguishes null specifically, so sometimes instead of “Replace Values,” you might also use "Replace Errors or Transform" to Replace Values but for nulls, “Replace Values” works fine.
Emphasizing “enter an appropriate value to replace the nulls” makes it clearer that nulls are being handled.

Profiling Data to Understand Column Quality

Enable Column Quality, Column Distribution, and Column Profile from the View tab.

These tools help identify empty values, duplicates, and value distribution issues before analysis.

Reshaping Data with Pivot and Unpivot

Unpivot converts columns into rows and is useful for monthly or repeated values. Pivot converts rows into columns when categories should appear as separate fields.

This section introduces unpivoting, a crucial transformation.

Key Steps:

Rename ResellerSalesTargets to Targets

Unpivot monthly columns (M01–M12).

Apply correct data types

Merging Queries and Disabling Load

Use Merge Queries to combine related tables. After merging ColorFormats into Product, disable loading for ColorFormats to keep the data model clean.

Steps:

Select ColorFormats query/table

Right-click the top-left corner of your data table (above the first row header, to the left of the first column header). Select "Use First Row as Headers" from the context menu.

This action will automatically rename the existing column headers to the values present in the first row and promote that row's data to be the official headers for your table.

Merge Product with ColorFormats

Use Left Outer Join

Since ColorFormats is only used for merging:

Disable Enable Load for the ColorFormats query.

This keeps your model clean and optimized.

You can also follow a similar approach to append queries.

Here is a quick explanation of the difference between the two:

Merge Query: Combines tables horizontally using a common column (adds new columns).

Similar to SQL JOIN

Append Query: Combines tables vertically by stacking rows (adds new rows).

Similar to SQL UNION

Simple Rule to Remember
Merge = JOIN → add columns
Append = UNION → add rows

Using the Advanced Editor

The Advanced Editor allows you to view and edit the M code behind each transformation. While optional for beginners, it provides deeper insight and control over Power Query logic.

Loading the Data and Saving the Report

Select Close and Apply to load all queries. Confirm that seven tables are loaded.

Save the report using File > Save As, apply pending changes if prompted, and close Power BI Desktop.

Summary

In this guide, you learned how to clean, transform, and prepare data in Power BI using Power Query, from resolving data quality issues and reshaping tables to merging queries and building a reliable data model ready for analysis. By now, you’re all set to tackle your own datasets and start uncovering meaningful insights!

Final Thought

Cleaning and transforming data is the foundation of effective Power BI analysis. Power Query provides powerful tools to remove duplicates, handle missing values, profile data, reshape tables, and prepare a reliable data model for reporting. Mastering these skills will make your dashboards more accurate, efficient, and professional.

Getting Data from Multiple Sources in Power BI: A Complete Beginner-Friendly Guide

Ibrahim Abdulrasaq — Wed, 31 Dec 2025 09:23:51 +0000

Introduction

The foundation of every successful Power BI report is reliable data ingestion. No matter how visually appealing your dashboards are, if the underlying data is incomplete, inconsistent, or poorly understood, the insights will be misleading.

In real-world business environments, data rarely comes from a single source. As a Data Analyst, you may need to work with:

Excel files
CSV text files
SQL Server databases
JSON APIs
PDF reports
SharePoint folders

All within the same project.

Power BI is designed to handle this complexity through its powerful Get Data and Power Query capabilities.

In this blog, you’ll learn how to connect to multiple data sources in Power BI, preview the data, and assess its quality before building your data model. By the end, you’ll be confident working with diverse data sources and preparing them for meaningful analysis.

High-Level Overview of Power BI Data Architecture

In this workflow, Power BI operates as the central hub where data from multiple sources is brought together and prepared for analysis.

Our architecture consists of:

Power BI Desktop → reporting, modeling, and development environment
Multiple data sources, such as:
- Excel and Text/CSV files
- SQL Server databases
- JSON and PDF files
- SharePoint folders
Power Query Editor → for cleaning, transforming, and profiling data

All data flows into Power BI through Power Query, where it is reviewed and prepared before loading into the data model.

What You’ll Accomplish in This Guide

In this step-by-step walkthrough, you will:

Open and configure Power BI Desktop
Connect to data from Excel, CSV, Database, SQL Server, JSON, PDF, and SharePoint
Preview and understand source data using Power Query
Use Column Quality, Column Distribution, and Column Profile
Identify common data quality issues early
Prepare datasets for modeling and reporting

Getting Started with Power BI Desktop

To practice along with this guide, first download the practice files from the link below:

🔗 https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/Allfiles/Labs/01-get-data-in-power-bi/01-get-data.zip

After downloading:

Extract the folder.
Open 01-Starter-Sales Analysis.pbix in Power BI Desktop.

This starter file disables automatic relationship detection so you can focus specifically on data ingestion and profiling.

Getting Data from SQL Server

Enterprise-level data is often stored in relational databases. Power BI connects easily to SQL Server.

Steps to connect:

Go to Home → Get Data → SQL Server

Enter:
- Server: localhost
- Database: leave blank

Select Windows Authentication (select Windows > Use my current credentials, and then Connect).

Select OK if you receive a warning that an encrypted connection cannot be established.

In the Navigator pane. Expand the AdventureWorksDW2020 database

Select the following tables:
- DimEmployee
- DimEmployeeSalesTerritory
- DimProduct
- DimReseller
- DimSalesTerritory
- FactResellerSales

Click Transform Data

Power Query Editor opens with six queries loaded from SQL Server.

Previewing Data in Power Query Editor

Power Query allows you to understand the data before loading it into the model.

Queries Pane

Each table appears as a separate query on the left. Selecting a query displays a preview of its contents.

Dimension Tables (Dim)

Examples:

DimEmployee:one row per employee

DimProduct:one row per product

DimReseller:one row per reseller

DimSalesTerritory:regions, countries, and groups

Fact Tables (Fact)

FactResellerSales: one row per sales order line

Understanding the difference between fact and dimension tables is essential for proper star-schema data modeling in Power BI.

Using Power Query Data Profiling Features

Power Query includes built-in tools to help assess data quality before modeling.

Column Quality

Enable:

View → Column Quality

This reveals:

Percentage of valid values
Empty (null) values
Errors

Example insight:

The Position column in DimEmployee contains 94% empty values, signaling a potential data quality issue.

Column Distribution

Enable:

View → Column Distribution

You can now see:

Number of distinct values
Number of unique values

Example:

EmployeeKey shows the same distinct and unique count → meaning every row is unique (useful when creating keys and relationships).

Column Profile

Enable:

View → Column Profile

Then select a column, such as BusinessType in DimReseller.

You may notice inconsistent labels:

“Warehouse”
“Ware House” (misspelled)

This inconsistency must be corrected before analysis to prevent inaccurate grouping or reporting errors.

Getting Data from Text/CSV Files

Flat files are extremely common in reporting workflows.

Importing a CSV file

Step 1: Home → Get data → Text/CSV

Step 2: Select ResellerSalesTargets.csv

This file contains:

One row per salesperson per year
Monthly sales targets
Hyphens instead of null values

Repeat the process to import "ColorFormats.csv", which contains color formatting values.

Getting Data from Excel Files

Excel remains one of the most widely used business data tools.

To import Excel data:

Step 1: Home → Get Data → Excel

Step 2: Select the Excel file

Step 3: Then click Transform Data
Excel files are ideal for:

Budgeting and finance sheets
Manual business inputs
Operational logs and trackers

Getting Data from JSON Files

JSON files are commonly generated by APIs and web-based applications.

Steps:

Step 1: Home → Get Data → JSON

Step 2: Select the JSON file or API export

Step 3: Power Query expands nested structures
Step 4: Flatten and transform fields as needed

JSON often requires extra transformation because of its hierarchical format.

Getting Data from PDF Files

Power BI can extract structured tables from PDF documents.

Steps:

Step 1: Home → Get Data → PDF

Step 2: Select the PDF file
Step 3: Choose detected tables
Step 4: Transform in Power Query

Useful for:

Financial statements
Bank reports
Compliance or regulatory documents

Getting Data from SharePoint Folders

SharePoint is widely used for collaborative file storage across organizations.

Steps:

Step 1: Home → Get Data → SharePoint Folder

Step 2: Enter the SharePoint site URL and authenticate

Step 3: Filter and combine files as needed

This approach is ideal when working with "multiple files stored in a shared location".

Why Data Profiling Matters

Before building dashboards, you must:

Identify missing values
Detect inconsistent labels
Validate key columns for relationships
Understand value distributions

Skipping this step can lead to:

Broken relationships
Incorrect KPIs
Misleading insights

Power Query ensures your data is 'accurate, reliable, and business-ready before visualization.

Conclusion

Getting data from multiple sources is a core skill for every Power BI data analyst. Power BI makes this process seamless by supporting a wide range of data connectors and providing powerful tools to preview and profile data before modeling.

By combining SQL Server, Excel, CSV, JSON, PDF, and SharePoint data in Power BI, you can build comprehensive, enterprise-ready reports with confidence.

Mastering this step ensures your dashboards are not only visually appealing but also accurate, trustworthy, and truly impactful.

SQL for Data Analytics: A Must Read for Beginner Data Analysts Before Diving Into SQL

Ibrahim Abdulrasaq — Tue, 30 Dec 2025 11:09:37 +0000

SQL (Structured Query Language) is one of the most important tools for every data analyst. It allows you to retrieve, filter, combine, and analyze data stored in databases. But before jumping straight into writing SQL queries, it is essential to first build a strong foundation in core data concepts, analytical thinking, and basic statistics. These skills give meaning to the results you obtain with SQL and help you solve real world problems more effectively.

Before diving into SQL, every beginner data analyst should understand key data concepts, basic statistics, and develop a strong analytical mindset. These foundations provide context and enable effective problem solving once you start working with data using SQL.

Fundamental Concepts to Understand Before Learning SQL

(1) Relational Databases

SQL is used to communicate with relational databases, systems that store data in an organized, structured format using tables. In most organizations, data is spread across multiple related tables rather than one large file, and SQL helps analysts retrieve and connect this information efficiently.

Understanding how relational databases work is essential because SQL is not just about writing queries, it is about understanding the data structure you are working with.

(2) Database Structure

Before writing SQL queries, a beginner analyst should understand the key components of a database:

-Tables store data in rows and columns, similar to an Excel sheet

-Rows or Records represent a single entry such as one customer or one transaction

-Columns or Fields represent attributes such as name, price, date, or product

-Primary Keys are unique identifiers for each row such as Customer ID or Transaction ID

-Foreign Keys link one table to another, creating relationships between datasets

These concepts make it easier to understand how JOINs work in SQL and how different tables connect in real world databases.

(3) Data Types

Every column in a database has a specific data type, which determines the kind of values it can store. Common examples include:

-VARCHAR for text values
-INT for whole numbers
-FLOAT for decimal numbers
-DATE for date values

Data types affect calculations, storage, filtering, and data integrity. Using the wrong data type can lead to errors or inaccurate results.

(4) NULL Values

NULL does not mean zero or empty. It represents missing or unknown data. In SQL, NULL behaves differently and must be handled carefully.

For example:

You cannot filter NULL values using the equals operator.
Instead, you must use IS NULL or IS NOT NULL

A good analyst understands what NULL means and how it affects averages, aggregations, and analysis outcomes.

Essential Skills Beyond SQL

Learning SQL alone is not enough to become a strong data analyst. The following complementary skills make your SQL knowledge more meaningful and practical.

(1) Analytical Mindset and Problem Solving

Data analysis is not just about running queries. It is about asking the right questions and interpreting results meaningfully.

A good analyst:

-Looks for trends, patterns, and anomalies

-Understands what the data is really saying

-Translates numbers into insights and decisions

SQL retrieves data. Your analytical mindset gives it meaning.

(2) Basic Statistics

Before analyzing data using SQL, beginners should understand key statistical concepts such as:

-Mean, median, and mode
-Variance and standard deviation
-Correlation and distribution patterns

Statistics help you:

-Interpret query results
-Avoid misleading conclusions
-Support insights with evidence

Without statistics, SQL results are just numbers, not insights.

(3) Excel Proficiency

Excel is one of the best starting tools for beginner analysts. Many data manipulation concepts in Excel translate naturally to SQL.

For example:

-Sorting and filtering work like SQL ORDER BY and WHERE

-Pivot tables are similar to SQL aggregations

-VLOOKUP works in a similar way to SQL JOIN operations

A strong Excel foundation makes learning SQL faster and more intuitive.

(4) Data Cleaning Concepts

In the real world, data is rarely clean. Before analysis, an analyst must know how to:

-Identify missing values
-Remove duplicates
-Fix inconsistent formats
-Standardize data

SQL is often used for data cleaning tasks, so understanding these concepts helps you write more meaningful and organized queries.

(5)Business Understanding

Beyond technical skills, a great data analyst understands the business context behind the data.

This helps you:

-Ask relevant questions
-Focus on metrics that matter
-Provide insights that support decision making

Without business understanding, analysis becomes just reporting, not problem solving.

Understanding SQL Through a Simple Analogy: Imagine SQL as a Detective Solving a Case 🕵️‍♂️🔎

Imagine SQL as a brilliant detective, not chasing criminals, but uncovering insights hidden inside data.

Every dataset is a crime scene.
Every table is a file of evidence.
Every query is a step toward solving the mystery.

Here is a simple illustration of how SQL works like a detective, using basic SQL commands:

SELECT: Gathering Clues
A detective focuses only on useful evidence. SELECT helps us pick the exact columns we need.

↪️ Command illustration:

SELECT name, salary
FROM employees;

We extract only the clues that matter.

WHERE: Filtering Suspects
A detective narrows down suspects based on facts. WHERE does the same.

↪️ Command illustration:

SELECT *
FROM employees
WHERE department = 'Finance';

From many records, we zoom in on the relevant ones.

ORDER BY: Arranging the Evidence

↪️ Command illustration:

SELECT name, score
FROM students
ORDER BY score DESC;

The most important evidence appears first.

GROUP BY: Finding Patterns

↪️ Command illustration:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Hidden trends and relationships become clearer.

JOIN: Connecting the Dots

SELECT orders.order_id, customers.name
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;

Separate clues come together to form one complete story.

SQL is not just a query language. It is a mindset. It teaches us to investigate, question, connect, and discover meaning from data. And just like a great detective, the more cases you solve, the sharper your thinking becomes.

Final Thoughts

SQL is a powerful and indispensable tool for every data analyst. However, its real strength comes from the foundation you build before using it. Understanding relational databases, statistics, analytical thinking, and core data concepts ensures that you do not just write queries. You interpret results correctly and provide meaningful insights.

Once these fundamentals are clear, learning SQL becomes more intuitive, practical, and valuable in your data analytics journey.