Microsoft Power BI for Business Intelligence - Power BI 2026

You’ve probably heard this before:

“Learn Power BI. It’s in demand.”

But no one really explains what that means… or why this tool even exists.

So before we jump into buttons and dashboards, let’s get something straight.

Power BI is not just another software.

It’s a self-service business intelligence tool.

And that phrase matters.

What does “self-service business intelligence” actually mean?

Simple.

You install Power BI on your computer, just like Excel or Word.

You don’t need a technical team to run it for you.
You don’t need to call Microsoft every time you want a report.

You:

• Connect your data

• Clean and transform it

• Model it

• Analyze it

• Build dashboards

All by yourself.

That’s self-service.

And the “intelligence” part?

That’s where insights come in.

Power BI helps you turn raw data into decisions.

Why use Power BI instead of other tools?

There are many Business Intelligence tools in the market. Each does roughly the same core job:

Take data → transform it → analyze it → visualize it.

But they differ in strengths, usability, and target audience.

Let’s quickly understand the ecosystem.

Microsoft Power BI

Strengths:

• User-friendly interface

• Strong data transformation with Power Query

• Massive integration options

• Excellent community support

• Affordable and widely adopted

If you want something powerful but easy to learn, Power BI is a great starting point.

Tableau

Strengths:

• Highly customizable visualizations

• Strong design flexibility

• Popular for creative dashboards

If visual creativity is your priority, Tableau shines.

Amazon QuickSight

Strengths:

• Strong ETL capabilities

• Works well with AWS ecosystem

• Handles large cloud datasets efficiently

Ideal for cloud-heavy organizations.

SAP BusinessObjects

Strengths:

• Enterprise-level governance

• Strong reporting structures

• Suitable for process-heavy organizations

Often used in large enterprises with structured approval systems.

Looker Studio

Strengths:

• Web-based

• Works beautifully with Google ecosystem

• Great for startups and modern data stacks

No installation required. Runs in your browser.

IBM Cognos Analytics

Strengths:

• Robust reporting

• Enterprise-focused

• Strong governance capabilities

Another enterprise-grade BI platform.

Sisense

Strengths:

• Handles large datasets efficiently

• Scalable architecture

• Known for performance

Used when speed and scale are critical.

So why are we learning Power BI?

Because it balances:

• Ease of use

• Powerful transformation

• Strong modeling

• Professional visualization

• Real business impact

It’s beginner-friendly.
But also enterprise-capable.

That’s rare.

What you’ll gain from this course

By learning Power BI, you’ll be able to:

• Connect multiple data sources

• Clean messy datasets

• Build data models

• Create dashboards that deliver insights

• Answer business questions with clarity

Not just build charts.

Build decisions.

Questions this introduction answers

• What is Power BI?

• What does self-service business intelligence mean?

• Is Power BI better than Tableau?

• What are Power BI competitors?

• Which BI tool should I learn?

• Why is Power BI popular?

If you’ve been wondering whether learning Power BI is worth it, this is your starting point.

You’ve heard the name.

Power BI.

Everyone talks about it.

Recruiters mention it.
Managers ask for it.
Job descriptions demand it.

But the real question is simple:

Why should you actually use Power BI?

In this video, we break it down in plain language. No hype. Just real reasons why this Business Intelligence tool has become so popular among data professionals.

Because here’s the truth.

Collecting data is easy.

Turning that data into insights, decisions, and clean dashboards? That’s where most people struggle.

And this is where Power BI steps in.

Here’s what makes it powerful:

Data Integration and Connectivity

• Connect to Excel, CSV, JSON, XML, databases, web sources

• Pull data from Microsoft SQL Server, Oracle, Azure, Google Analytics

• Combine multiple data sources into one place

• Connect to over 100 different data connectors

Data Transformation and Preparation

• Clean messy data

• Standardize formats

• Remove duplicates

• Build relationships between tables

• Shape and model your data properly

Advanced Analytics and Insights

• Create calculations and measures

• Extract meaningful insights from large datasets

• Perform complex analysis without heavy coding

Interactive Dashboards and Visualizations

• Build dynamic charts and reports

• Use filters and slicers

• Create dashboards that management can actually understand

Ease of Use

• Drag-and-drop interface

• Beginner-friendly

• No need to be a hardcore programmer

Collaboration and Sharing

• Publish reports to Power BI Service

• Share dashboards with managers or teams

• Work together on reports

Cost Effectiveness

• Free desktop version available

• Enterprise plans cost less compared to many competitors

Scalability and Flexibility

• Handle millions of rows of data

• Work with growing datasets

Security and Governance

• Protect sensitive information

• Apply row-level security

• Control who sees what

If you’ve searched:

• Why use Power BI?

• Is Power BI worth learning?

• Benefits of Power BI for beginners

• Power BI vs other BI tools

• What can Power BI actually do?

This video gives you the full picture.

Power BI is not just a reporting tool.

It’s a complete Business Intelligence platform that supports your entire data journey, from collecting raw data to presenting executive-level dashboards.

And now that you understand why it matters, it’s time to stop watching and start building.

You’ve used Excel.

You’re comfortable with it.

You can build pivot tables, write formulas, maybe even create dashboards.

So the real question is:

If Excel works… why learn Power BI?

That’s exactly what we clear up in this lesson.

Excel is powerful. No doubt.

Let’s be honest.

Excel is:

• Easy to start with

• Perfect for quick analysis

• Great for individual tasks

• Flexible for small transformations

• Familiar to almost everyone

If you’re in operations, sales, finance, or supply chain, Excel gets things done fast.

For personal analysis or quick reporting, it’s fantastic.

But it has limits.

Where Excel starts struggling

When you try to:

• Build highly interactive dashboards

• Handle large datasets

• Automate recurring reports

• Share secure reports across large teams

• Maintain version control

• Scale analysis across departments

You start hitting friction.

Yes, you can force Excel to do these things.

But it becomes messy.

Manual updates. Broken formulas. Multiple versions floating around. Security concerns.

That’s where Power BI steps in.

What Power BI does better

Power BI is designed for:

• Automated data refresh

• Large-scale data modeling

• Interactive dashboards

• Cross-functional collaboration

• Secure data sharing

• Advanced visualizations

Things that feel “complex” in Excel feel natural in Power BI.

Especially when reports need to update daily, weekly, or monthly without manual intervention.

Excel vs Power BI: What’s different?

Here’s a practical comparison:

In Excel, you use:

• Spreadsheets

• A1 cell references

• Pivot Tables

• Excel formulas

• Manual or semi-automated reports

In Power BI, you use:

• Report View

• Interactive dashboards

• Custom visuals

• Power BI Service for sharing

• Automated refresh pipelines

Different interface. Same goal.

Turn data into insight.

What’s common between both?

This is important.

Both Excel and Power BI share:

• Power Query

• Data Modeling

• DAX (Data Analysis Expressions)

In fact, Power BI was born from Excel’s advanced capabilities.

It evolved into a full business intelligence platform.

So if you already know Excel, you’re not starting from zero.

You’re upgrading.

When should you use Excel?

Use Excel when:

• The dataset is small

• The task is individual

• You need quick, one-time analysis

• Collaboration is minimal

When should you use Power BI?

Use Power BI when:

• Data is growing

• Multiple teams need insights

• Reports must be automated

• Dashboards need to be interactive

• Security and governance matter

Questions this video answers

• Is Power BI better than Excel?

• Should I learn Power BI if I already know Excel?

• What is the difference between Excel and Power BI?

• Can Power BI replace Excel?

• When should I use Power BI instead of Excel?

If you’re transitioning from Excel to Business Intelligence, this lesson gives you the mindset shift you need.

Your CEO walks into the room and asks:

“How is the business doing?”

Not a 20-page report.
Not raw spreadsheets.
Just a clear answer.

That’s what an executive dashboard is for.

In this project, we’re building a Power BI Executive Dashboard designed for top-level decision makers, CEO, CMO, or any leadership role.

An executive dashboard is not about tiny details.

It answers big questions fast:

• What’s happening in the business right now?

• Are we growing or slowing down?

• Where are the risks?

• What needs attention today?

Think of dashboards that show:

• Total shipments

• Pending deliveries

• Revenue and financial health

• Trend over time

• Operational performance

• Key business metrics at a glance

Clear. Focused. Strategic.

In this hands-on Power BI project, we’ll go much deeper than before.

Here’s what we’ll focus on:

Data Integration and Connectivity

• Importing multiple datasets

• Connecting structured business data

• Combining different data sources

Data Transformation and Preparation

• Cleaning messy datasets

• Standardizing columns

• Preparing executive-level metrics

• Handling real-world data issues

Data Modeling

• Building relationships between tables

• Structuring data properly

• Preparing for scalable reporting

This isn’t just about creating charts.

It’s about building a dashboard that tells leadership exactly what they need to know in seconds.

If you’ve searched:

• How to build an executive dashboard in Power BI

• Power BI CEO dashboard example

• What is an executive dashboard?

• Power BI business performance dashboard

• How to design dashboards for top management

This project will walk you through it step by step.

We’re about to move from small analysis to serious reporting.

You open Power BI.

You click “Get Data.”

And suddenly… there are 100+ options.

Excel. CSV. JSON. Databases. Web. APIs. Cloud.

It’s overwhelming.

So before we start connecting things randomly, let’s understand what data sources Power BI actually supports, and how to think about them.

Because the tool is powerful. But only if you know what it can connect to.

High-Level Categories of Data Sources in Power BI

Power BI organizes data sources into broad categories:

• Files

• Databases

• Online Services

• Cloud Storage

• Web & APIs

• Microsoft Services

• Other data sources

• Live and Direct connections

In this lesson, we focus on the simplest and most common one: Files.

File-Based Data Sources in Power BI

These are the easiest starting point for beginners.

1. Excel Files

4

• Most commonly used source

• Must be stored locally on your system

• Structured tables work best

• Perfect for small to medium datasets

If you’ve already worked in Excel, this is your comfort zone.

2. CSV or Text Files

4

• Lightweight format

• Often exported from systems

• Can be opened in Notepad or Excel

• Easy to import

Many systems export reports as CSV. Power BI handles them easily.

3. XML Files

4

• Used in structured web data

• Often linked to RSS feeds

• Good for hierarchical data

If a website provides structured exports, XML might be the format.

4. JSON Files

4

• Common with APIs

• Used by modern applications

• Supports nested data

If an app gives you an export file, chances are it’s JSON.

5. PDF Files

4

Yes, Power BI can import PDFs.

But there’s a catch.

• The PDF must contain structured tables

• Plain text PDFs won’t work properly

• Tables must be clearly formatted

Power BI extracts tables, not paragraphs.

Why this matters

In real business scenarios, data doesn’t always come in perfect Excel files.

It might come as:

• A system export in CSV

• An API response in JSON

• A financial report in PDF

• A website feed in XML

Power BI is built to handle all of it.

Understanding your source format helps you import correctly and avoid confusion later.

Practical outcome

After this lesson, you’ll:

• Understand the main file-based data sources in Power BI

• Know when to use Excel vs CSV vs JSON

• Avoid common import mistakes

• Feel more confident exploring “Get Data” options

This is just the beginning.

You can’t build a serious executive dashboard without serious data.

And in real businesses, data doesn’t sit nicely in one clean Excel file.

It’s everywhere.

Some of it is in Excel.
Some in CSV.
Some in XML.
Maybe even in a PDF or text file.

That’s exactly what we’re dealing with in this project.

In this lesson, we start building our Executive Dashboard by importing multiple data sources into Power BI.

We begin with a blank report.

Empty canvas.
No visuals.
No shortcuts.

Then we explore our project folder.

Inside it, we find:

• Excel files

• CSV files

• XML files

• Text files

• PDF documents

• A product launch folder

• An additional folder with more data

This is what real-world data looks like.

Scattered. Mixed formats. Different structures.

Here’s what you’ll learn in this step:

Importing Excel Files

• Use Get Data → Excel Workbook

• Preview datasets before loading

• Load Employees data

• Load Fulfillment data

• Understand why large datasets take time to load

Managing Imported Tables

• Why Power BI sometimes names tables “Sheet1”

• How to rename queries properly

• How to avoid confusion early in your model

• Use right-click rename or Table Tools to rename tables

• Keep your data model clean from day one

Saving Your Project

• Save your Power BI file properly

• Name it clearly as Executive Dashboard

• Build good habits from the start

If you’ve searched:

• How to import multiple Excel files into Power BI

• How to rename tables in Power BI

• Why does Power BI show Sheet1 and Sheet2?

• How to load large datasets in Power BI

• Power BI executive dashboard project

This lesson walks you through the exact process in a simple way.

By the end of this video, you will have:

• Created a new Power BI report

• Imported two Excel datasets

• Renamed them properly

• Saved your Executive Dashboard project

And this is just the beginning.

You’ve imported Excel files.

Now it’s time to bring in CSV data.

And here’s something you’ll notice immediately.

The experience is slightly different.

Not complicated. Just different.

In this lesson, we connect CSV files to Power BI and understand what changes compared to Excel imports.

Why CSV behaves differently from Excel

When you import an Excel file, Power BI asks:

• Which sheet do you want?

• Which table should I load?

Because Excel can contain multiple sheets.

But a CSV file?

It can only hold one flat dataset.

That’s it.

So when you import a CSV file, Power BI skips the sheet selection step and takes you directly to the preview.

Cleaner. Faster. No confusion.

Steps Covered in This Video

We walk through:

• Going to Get Data

• Selecting Text/CSV

• Navigating to the project folder

• Importing the Orders CSV file

• Reviewing the preview window

• Loading the data directly into the model

And here’s a small but important detail:

With CSV files, you don’t need to rename the table after import.

Why?

Because the file name becomes the table name automatically.

Unlike Excel, there’s no sheet name involved.

What You’ll Notice in the Preview

When the CSV loads:

• You see one structured table

• No sheet selection

• No hierarchy

• No additional navigation steps

It’s straightforward.

That’s one reason CSV is widely used for data exports from systems.

Practical Outcome

By the end of this video:

• Your Orders table (CSV) is loaded into Power BI

• You understand why CSV imports look different from Excel

• You can confidently import multiple CSV files

• Your project now contains both Excel and CSV data sources

This is how real-world projects work.

In real life, data is messy.

It doesn’t politely sit in one Excel file waiting for you.

Sometimes it’s inside an XML file.
Sometimes buried in a PDF.
Sometimes structured. Sometimes chaotic.

And if you’re building an executive dashboard, you don’t get to ignore any of it.

In this lesson, we continue building our Power BI Executive Dashboard by importing XML and PDF data sources.

First, we import the XML file.

Unlike Excel, XML files often contain hierarchical data. That’s why Power BI opens the Navigator view instead of directly showing a flat table.

Here’s what you learn:

Importing XML in Power BI

• Go to Get Data → More

• Select XML

• Navigate to your file

• Understand why XML shows hierarchical preview

• Select the correct table

• Load the data properly

Now your campaign data is successfully added to the model.

Next comes something more interesting.

PDF files.

Most beginners don’t even realize Power BI can import tables from a PDF.

But it can.

In our project folder, we have two PDFs:

• One contains structured tables

• One contains plain text (no tables)

Important lesson: Power BI can extract tables from PDFs, but it cannot magically convert unstructured email text into clean table data.

So we select the correct PDF file that contains tables.

When importing, you’ll notice:

• Multiple tables detected from the same PDF

• Page views vs table views

• Why you should select tables instead of full pages

• How each detected table becomes a separate dataset

After loading, you’ll see:

• Table001

• Table002

• Table003

• Table004

This is normal.

Now comes good modeling practice.

You rename them clearly:

• Products1

• Products2

• Products3

• Products4

Clean naming now prevents confusion later.

If you’ve searched:

• How to import XML file in Power BI

• How to import PDF into Power BI

• Why does Power BI show multiple tables from PDF?

• How to extract tables from PDF in Power BI

• Can Power BI read email text from PDF?

This lesson walks you through it in a practical way.

By the end of this video, you have:

• Imported XML campaign data

• Imported structured tables from PDF

• Understood PDF table detection

• Renamed tables properly

• Expanded your data model with multiple file types

And we’re not done yet.

There’s still one PDF left that doesn’t contain tables.

You get an email from your boss.

Not a fancy Excel file.
Not a structured CSV.

Just a flat email with numbers inside.

Product goals. Targets. Budgets.

And now the question is:

How do you bring that into Power BI?

You don’t ignore it.
You don’t wait for someone to “send it properly.”

You use Enter Data.

The Situation

The email contains:

• Year-wise product launch goals

• Actual products launched

• Budget allocations (in INR) for upcoming years

But it’s not in a file format Power BI can directly connect to.

So we manually create a table.

Step 1: Use “Enter Data” in Power BI

Instead of importing a file, we:

• Go to Get Data

• Choose Enter Data

• Create a new table

• Name it Product Goals

This is perfect for small datasets, targets, assumptions, or management inputs.

Step 2: Create the Required Columns

We define four columns:

• Year

• Products Goal

• Products Actual

• Budget_INR

This mirrors exactly what the business cares about.

No extra noise.

Step 3: Enter Historical Data (2018–2024)

From the email:

• 2018 → 9 products

• 2019 → 14

• 2020 → 10

• 2021 → 4

• 2022 → 13

• 2023 → 0

• 2024 → 6

Goals and actuals are aligned for past years.

Budgets for these years? Not provided.

So we enter 0 for now and plan to confirm later.

That’s real-world data work. Sometimes you don’t have everything.

Step 4: Enter Future Targets (2025–2027)

From the email:

• 2025 → Goal: 6

• 2026 → Goal: 3

• 2027 → Goal: 4

Budgets mentioned:

• 20–30 Lakhs

• 30–40 Lakhs

• 40–50 Lakhs

Instead of ranges, we simplify and use upper estimates:

• 300,000 INR

• 400,000 INR

• 500,000 INR

You can always refine later.

Dashboards evolve.

Why This Matters

In real projects:

• Data comes in emails

• Targets come in meetings

• Assumptions come verbally

If you wait for perfect data structure, you’ll never build anything.

Power BI allows you to manually create structured tables for these cases.

That flexibility is powerful.

Practical Outcome

After this lesson:

• You’ve created a custom Product Goals table

• You understand how to manually input business data

• You’ve structured goals, actuals, and budgets properly

• Your model now includes performance tracking data

This unlocks future analysis like:

• Goal vs Actual comparison

• Budget vs performance analysis

• Future projections

And we’re not done yet.

There’s one more file left to import.

You’ve imported Excel.
You’ve handled XML.
You’ve even pulled tables out of a PDF.

Now comes something slightly messy.

A plain text file.

No neat columns.
No clean table.
Just raw information.

And when you try to import it, it doesn’t even show up at first.

Why?

Because Power BI filters file types by default.

It only shows certain extensions like .txt or .csv unless you switch the file type dropdown to “All Files.”

That small detail trips up a lot of beginners.

In this lesson, you learn:

Importing a Text or CSV File in Power BI

• Use Get Data

• Change file filter to “All Files”

• Locate hidden text files

• Understand how Power BI previews raw text data

• Load unstructured text into the model

When the file loads, you’ll notice something important.

Since it wasn’t a structured table, Power BI treats it differently.

That means clean naming becomes even more important.

So you rename the dataset properly:

Product Bundles

Clear names now will save you confusion later when we start transforming and modeling.

At this point, your executive dashboard project includes:

• Excel files

• XML data

• PDF tables

• Text file data

This is what real-world data integration looks like.

Different formats.
Different structures.
One reporting layer.

If you’ve searched:

• How to import text file in Power BI

• Why can’t I see my file in Power BI?

• How to change file filter in Power BI

• Import CSV or TXT file into Power BI

• How to rename datasets in Power BI

This lesson clears it up in simple steps.

Most people think Power BI is just for Excel files.

It’s not.

If your company runs on databases, cloud tools, CRMs, analytics platforms, or internal systems, Power BI can connect to almost all of them.

In this lesson, we explore the bigger ecosystem of Power BI data connectors.

Because this is where Power BI becomes serious.

1. Database Connections

When you go to Get Data → More → Database, you’ll see a massive list.

Here are some of the most common ones:

• Microsoft SQL Server

• Oracle Database

• MySQL

• PostgreSQL

• IBM Db2

• Microsoft Access

And beyond relational databases, you’ll also see:

• MongoDB

• SAP HANA

These require:

• Server authentication

• Credentials

• Sometimes IP whitelisting

For this project, we don’t use them. But in real corporate environments, this is extremely common.

If you already have access to a database, try connecting it just to understand how authentication works.

That experience matters.

2. Online Services

Power BI also connects directly to cloud platforms.

Under Online Services, you’ll find connectors for:

• SharePoint Online

• Microsoft Dynamics 365

• Salesforce

• Google Analytics

• Adobe Analytics

• Azure DevOps

• LinkedIn Learning

This is powerful.

You can pull CRM data, website traffic, marketing analytics, and project management data directly into one dashboard.

No manual exports required.

3. Other Connectors

You’ll also see connectors for:

• SurveyMonkey

• Google Sheets

• R scripts

• Python scripts

• Web APIs

Power BI isn’t just a reporting tool. It’s an integration hub.

Live Web Connection Demo

To demonstrate how flexible Power BI is, we connected to a live website.

Specifically, a Wikipedia page listing countries where Burger King operates.

Here’s what happened:

• We selected Get Data → Web

• Pasted the Wikipedia URL

• Chose anonymous authentication

• Previewed available tables

• Selected the Asia table

• Loaded it into Power BI

And just like that, we had live web data inside our model.

Important insight:

If that Wikipedia table changes tomorrow, your Power BI table updates on refresh.

That’s dynamic integration.

After the demo, we deleted the table since it wasn’t needed for analysis.

But the takeaway is clear.

Power BI can connect to:

• Databases

• Cloud platforms

• APIs

• Websites

• Scripts

• Enterprise systems

Practical Outcome

By the end of this lesson, you understand:

• How Power BI connects to SQL databases

• How authentication works at a high level

• What online services can integrate directly

• How web connectors function

• How vast the Power BI connector ecosystem really is

You’re no longer limited to files.

You can now think bigger.

You imported the data.

Great.

But here’s the uncomfortable truth.

Raw data is almost never ready for analysis.

It has extra rows.
Wrong formats.
Test entries.
Garbage at the top.
Garbage at the bottom.

And if you build dashboards on top of that?

You’re building on sand.

This is where data transformation begins.

In this section, we move into one of the most important parts of Power BI: cleaning and shaping your data before analysis or data modeling.

Because clean data = trusted insights.

In this lesson, we start with basic transformations, specifically row-level transformations.

What is Data Transformation?

It’s the process of preparing raw data so it becomes usable, reliable, and analysis-ready.

Power BI gives you powerful tools inside Power Query to do this.

And yes, it can get complex. Because humans are messy when storing data.

Row-Level Transformations Covered in This Video

Removing Top Rows

• Delete the first few rows

• Useful when data has headers, notes, or test entries

• Clean up unwanted metadata

Removing Bottom Rows

• Remove footer rows

• Eliminate summary notes or irrelevant records

• Keep only meaningful data

Removing Alternate Rows

• Remove every second row

• Useful for sampling

• Helpful in certain data science scenarios

Keeping Specific Rows

• Retain only selected rows

• Remove everything else

• Narrow your dataset intentionally

These are small actions.

But they have a big impact.

If you’ve searched:

• How to remove rows in Power BI

• Remove top rows in Power Query

• Delete bottom rows in Power BI

• Remove alternate rows Power BI

• How to clean raw data in Power BI

This lesson gives you the foundation.

Before we build executive dashboards.

Before we write DAX.

Before we create relationships.

We clean.

Because transformation is not optional.

It’s mandatory.

You’ve imported everything.

Excel files.
CSV files.
Manually entered tables.
Web data.

Now comes the part most people skip.

Cleaning it properly.

Because loaded data is not the same as usable data.

In this lesson, we move into the Power Query Editor and apply structured transformations across all tables.

Step 1: Promote First Row as Headers

Many tables were showing:

• Column1

• Column2

• Column3

That’s not usable.

So we:

• Open Transform Data

• Select each table

• Click Use First Row as Headers

This promotes actual column names into headers.

Important detail:

Some tables did not need this.
For example:

• Tables already structured correctly

• Tables manually created inside Power BI

You don’t blindly apply transformations.
You check first.

Step 2: Scan for Unnecessary Columns

We quickly reviewed each table:

• Employees → No change needed

• Fulfillment → No change needed

• Orders → No change needed

• Customers → One column removed

In the Customers table, the “Sign Up Month” column was redundant.

Why?

Because we already had the full sign-up date.

So we removed it using:

Home → Remove Columns

This keeps the model clean and avoids duplication.

Step 3: Split the Product Bundles Table

This is where things get interesting.

The Product Bundles table had combined data like:

BundleID -- BundleName -- ProductIDs -- Price

All in one column.

That’s not analysis-ready.

So we split it.

First Split: By Leftmost Delimiter

• Go to Split Column → By Delimiter

• Use custom delimiter: --

• Split at leftmost delimiter

• Output as columns

This separated the first component.

Second Split: Split Remaining Data

• Select the second column

• Split again by --

• Choose “Each occurrence of the delimiter”

Now the data expanded into multiple structured columns.

Step 4: Rename Columns Properly

We renamed:

• Bundle ID

• Bundle Name

• Product IDs

• Bundle Price

And here’s a key insight:

Power BI grouped all renaming actions into one applied step.

Instead of:

Rename 1
Rename 2
Rename 3
Rename 4

It created one transformation using M code.

That’s efficient query design.

Power BI isn’t just recording clicks.
It’s building a transformation script.

What’s Done So Far

We have:

• Promoted headers

• Removed unnecessary columns

• Split combined data

• Renamed fields properly

• Cleaned structure across all tables

At a structural level, the data is ready.

But one critical thing is still missing.

Data types.

Without correct data types:

• Calculations break

• Sorting fails

• Measures behave incorrectly

And that’s what we fix next.

You cleaned the rows.

Now let’s fix the columns.

Because messy columns are just as dangerous as messy rows.

Wrong data types.
Combined information.
Fields that shouldn’t be calculated but are.

If you don’t fix this early, your analysis will break later.

In this lesson, we move into column-level transformations in Power BI.

These are fundamental data cleaning techniques you’ll use in almost every real project.

Splitting Columns

Sometimes a single column stores multiple pieces of information.

For example, a column might contain “Veg” and “Non Veg” in one place. But you want them separated.

With Split Column, you can:

• Break one column into two

• Separate values based on delimiter

• Handle non-applicable values properly

• Prepare fields for better analysis

This makes your dataset cleaner and more structured.

Merging Columns

The opposite scenario also happens.

You may have two columns that logically belong together.

Instead of keeping them separate, you merge them into one clean column.

Power BI allows you to:

• Combine multiple columns

• Control separators

• Create meaningful combined fields

Changing Data Types

This is one of the most important transformations.

Power BI automatically assigns data types, but it’s not always correct.

For example:

• Pizza ID should not be a number used in calculations

• Country ID should be text

• Price might need to be whole number or decimal

• Dates must be properly formatted

If you don’t fix data types:

• Aggregations may fail

• Calculations may behave incorrectly

• Visuals may show errors

You can manually change data types field by field.

Or…

Detect Data Type Automatically

If you’re unsure about a column’s format, Power BI can detect it for you.

It analyzes the data and assigns what it believes is the correct type.

You can also configure how many rows Power BI should inspect while detecting types.

If you’ve searched:

• How to split columns in Power BI

• How to merge columns in Power Query

• Change data type in Power BI

• Why is Power BI summing my ID column?

• Detect data type automatically Power BI

This lesson gives you the foundation.

Row cleaning + column cleaning = structured dataset.

And only after that should you move into data modeling and analysis.

You’ve cleaned the structure.

Headers are fixed.
Unnecessary columns removed.
Tables split properly.

But there’s one silent problem left.

Data types.

And if you ignore data types, your analysis will quietly break later.

In this lesson, we fix that properly.

Why Data Types Matter

If Power BI doesn’t know what kind of data it’s looking at:

• Numbers may behave like text

• Dates won’t sort correctly

• Calculations will fail

• Aggregations will behave strangely

So we move into the Transform ribbon and use Detect Data Type.

Step 1: Detect Data Type for a Single Column

Example: Employee ID

We:

• Select the column

• Click Detect Data Type

Power BI scans every row and decides:

• Whole number → 123 icon

• Decimal number → 1.2 icon

• Text → ABC icon

• Date → Calendar icon

• DateTime → Calendar + clock icon

This creates a new applied step called Changed Type.

Power BI checks the entire column, not just visible rows.

Step 2: Apply to Multiple Columns at Once

Instead of selecting columns one by one:

• Hold Shift

• Select multiple columns

• Click Detect Data Type

One applied step.
Multiple columns corrected.

Efficient and clean.

What We Verified in the Employees Table

After detection:

• Employee ID → Whole Number

• Initials, First Name, Last Name → Text

• Date of Birth → Date

• Date of Joining → DateTime

• Mobile Number → Whole Number

• Email → Text

• Annual Cost → Decimal

• Reporting Manager → Whole Number

Everything aligned correctly.

That’s what we want.

Important Correction: Product Bundles Table

Here’s where things got interesting.

When we auto-detected data types for Product IDs, Power BI misinterpreted the values.

It changed formatting in a way we didn’t want.

This is common.

Auto detection is helpful, but not perfect.

So we:

• Went to Applied Steps

• Removed the specific Changed Type step

• Manually set data types column by column

For Product IDs, we kept it as Text.

Because IDs are identifiers.
They are not numbers for calculation.

That distinction is critical.

Fixing Campaign Table Headers

Another issue surfaced.

The Campaigns table had incorrect header promotion.

Power BI accidentally promoted actual data as headers.

So we:

• Removed the Promoted Headers step

• Restored proper structure

Always review transformations.
Never assume automation is correct.

What’s Complete Now

We have:

• Correct column headers

• Clean structure

• Correct data types across all tables

• Manual corrections where needed

• Proper applied steps recorded in Power Query

At this stage, the data model is stable.

Not visually exciting.
But structurally solid.

And that’s what matters before analysis.

You’ve imported the data.

You’ve cleaned the rows.

Now it’s time to fix the text.

Because text fields are sneaky.

Extra spaces.
Wrong formats.
Mixed domains.
Hidden characters.

If you ignore them, they quietly break your analysis later.

In this lesson, we apply text transformations in Power BI using Power Query.

And here’s something powerful.

Power BI is contextual.

If you select a numeric column, you’ll see number-related options enabled.

If you select a text column, text-related options light up.

It understands your data type and adjusts the ribbon automatically.

That’s not small. That’s smart design.

Here’s what we applied in this transformation phase.

Lowercase Formatting

• Convert “MR” and “MISS” into lowercase

• Use Transform → Format → Lowercase

• Standardize text for consistency

Trim Spaces

• Remove leading and trailing spaces

• Applied to First Name and Last Name

• Prevent invisible spacing errors

Clean Non-Printable Characters

• Applied to Address column

• Removes hidden characters

• Improves data integrity

Replace Values

• Standardize email domains

• Replace example.com → example.net

• Replace example.org → example.net

• Ensure uniform company email domain

Renaming Applied Steps

This is critical.

As you apply transformations, the Applied Steps pane grows.

Instead of keeping vague names like “Replaced Value,” you rename them clearly:

• Lowercase Initials

• Trimmed Names

• Cleaned Address

• Replaced .com with .net

• Replaced .org with .net

Clean steps = easier debugging later.

Then we move to extraction.

Extracting State and Zip Code from Address

The address column contains structured information:

• Last 2 characters = State

• Last 5 characters = Zip Code

Instead of modifying the original column, we use Add Column.

Why?

To preserve the integrity of raw data.

We:

• Extract last characters

• Create a new column

• Split by space delimiter

• Separate state and zip code

• Rename new columns properly

Now your dataset is:

• Cleaner

• Standardized

• Structured

• Ready for analysis

If you’ve searched:

• How to trim text in Power BI

• Clean text Power Query

• Replace values in Power BI

• Extract text from column Power BI

• Split column by delimiter Power BI

• Add column vs transform column difference

This lesson walks you through real-world usage.

Data transformation isn’t flashy.

But it’s the difference between a dashboard that looks good and one that works correctly.

Your data looks fine at first glance.

Names are there. Emails are there. Addresses are there.

But look closer.

Random capitalization.
Extra spaces.
Inconsistent formatting.

Small things. Big impact.

Because messy text makes dashboards look unprofessional and filters behave strangely.

In this lesson, we clean it up using Text Transformations in Power Query.

1. Capitalizing Customer Names Properly

In the Customers table, the customer name needed fixing.

Goal:

• First letter of first name → Capital

• First letter of last name → Capital

• Everything else → Lowercase

How we did it:

• Select the column

• Go to Transform → Format

• Choose Capitalize Each Word

Now:

john doe → John Doe
mARIA SMITH → Maria Smith

Clean. Professional. Consistent.

2. Fixing Email Addresses

Emails should never have random capital letters.

So we:

• Select Email column

• Transform → Format → Lowercase

Result:

John.Doe@Example.COM → john.doe@example.com

Much better.

3. Cleaning Address Fields

Addresses often contain:

• Extra spaces

• Inconsistent formatting

We applied:

• Lowercase (for consistency)

• Trim (to remove leading and trailing spaces)

Trim is important.

Even one extra space can break relationships or cause duplicate-looking records.

4. Customer Support Table (VOC Column)

The VOC field needed trimming.

So we:

• Select the column

• Transform → Format → Trim

Clean text = reliable filtering later.

5. Branch Information Table

Address field again.

Same logic:

• Apply Trim

Always remove hidden spaces. Always.

Tables That Didn’t Need Changes

Some tables didn’t require text transformation:

• Fulfillment

• Orders

• Inventory

• Campaign

• Assets

• Product Goals

• Product Bundles

Important lesson:

Don’t transform for the sake of transforming.

Only fix what needs fixing.

Why This Matters

Text inconsistencies can cause:

• Duplicate values

• Broken relationships

• Incorrect groupings

• Ugly visuals

Proper formatting ensures:

• Clean filtering

• Accurate aggregation

• Professional presentation

It’s small cleanup work that prevents big problems later.

Raw text data looks harmless.

Until it ruins your dashboard.

Extra spaces.
Hidden characters.
Random capitalization.
Half-written labels.

If you don’t fix text properly, your filters break, your groupings split incorrectly, and your analysis becomes unreliable.

In this lesson, we break down the most important text transformations in Power BI, the ones you’ll use in almost every project.

Trim

Trim removes extra spaces.

• Leading spaces

• Trailing spaces

• Double or triple spaces

• Invisible gaps you can’t see

Humans miss them. Power BI doesn’t.

Clean

Clean removes non-printable characters.

These usually appear when:

• Data comes from multiple systems

• Files are copied from web sources

• Encoding issues occur

Clean strips out hidden symbols and unsupported characters.

Upper

Converts all text into full uppercase.

Example:
“pizza shop” → “PIZZA SHOP”

Useful when you want consistent formatting.

Lower

Converts all text into lowercase.

Example:
“Mr. JOHN” → “mr. john”

Helpful for standardization and case-sensitive comparisons.

Replace Values

Instead of manually editing rows one by one, you can:

• Replace abbreviations

• Standardize labels

• Correct repeated formatting errors

• Convert “veg” to “vegetarian”

• Replace domain names

• Fix recurring mistakes

One action updates the entire column.

Extract Text

Sometimes you don’t need the full string.

You may want:

• First few characters

• Last few characters

• Text before a delimiter

• Text after a delimiter

• A specific pattern

Example:
If product names have two words, you can extract only the first word.
If addresses contain state and zip code, you can isolate them.

This is extremely useful when preparing structured columns from messy text.

If you’ve searched:

• How to trim text in Power BI

• Clean function in Power Query

• Convert text to uppercase Power BI

• Replace values in Power BI

• Extract text before delimiter Power BI

• Text transformations in Power Query

These are your core tools.

Text cleaning is not glamorous.

But it’s what separates amateur dashboards from professional ones.

You can clean data.
You can fix headers.
You can change data types.

But if you don’t understand merge and append, your model will never scale properly.

This is where Power BI stops being a spreadsheet tool and starts behaving like a real data engine.

In this lesson, we break down two critical concepts:

• Appending → Adding rows

• Merging → Adding columns

And we apply both to our Products tables.

First: What Is Appending?

Think of appending like stacking data.

Same structure.
More rows.

Very similar to:

• SQL UNION

• Copy-pasting rows from one Excel sheet into another

If two tables have:

• Same columns

• Same headers

• Same structure

You append them.

What happens?

• Number of rows increases

• Number of columns stays the same

Applying Append to Products Tables

We had:

• Products 1

• Products 2

Both contained product details, just split across files.

So we:

1. Selected Products 1

2. Went to Home → Append Queries

3. Selected Products 2

But something went wrong.

Why?

Because the column headers did not match exactly.

When headers don’t match, Power BI creates new columns instead of stacking rows.

Lesson:

Appending only works cleanly when headers are identical.

So we:

• Renamed headers in Products 2

• Matched them exactly with Products 1

• Re-applied Append

Now:

Rows stacked perfectly.

Then we repeated the same logic for:

• Products 3

• Products 4

Result:

Products 1 now contains all product rows from Products 2 as well.

Products 3 contains all product rows from Products 4.

Clean stacking.

Now: What Is Merging?

Merging is different.

Instead of stacking rows, we attach columns from another table.

Very similar to:

• SQL JOIN

• Excel VLOOKUP

What happens?

• Number of rows stays the same

• Number of columns increases

The Challenge Before Merging

Products 3 table contained:

• Price

• Cost

• Launch Year

But it did NOT contain Product ID.

We needed a matching key.

So we created:

• An Index Column in Products 1

• An Index Column in Products 3

Now both tables had a matching field.

This becomes the bridge for merging.

Applying Merge

Steps:

1. Select Products 1

2. Go to Home → Merge Queries

3. Choose Products 3

4. Select Index column in both tables

5. Choose Full Outer Join

6. Click OK

Power BI confirms:
50 of 50 rows matched.

Then we:

• Expanded the merged table

• Selected all columns

• Removed prefix option

• Renamed fields properly

Now Products 1 contains:

• Product details

• Price

• COGS

• Launch Year

All in one clean table.

Cleaning Up

After merging:

• Removed Index columns

• Renamed applied steps

• Removed temporary transformation logic

We now have one final Products table ready for analysis.

The other product tables still exist in Power Query, but we won’t use them in reporting.

That brings us to the next step:

Managing the Queries pane properly so your model stays clean and readable.

Why This Matters

Understanding append vs merge determines:

• How you structure your model

• Whether your analysis is scalable

• Whether relationships will behave properly

• Whether your dashboards stay clean

If you misuse append and merge, your model becomes messy fast.

If you use them correctly, your model becomes powerful.

When your project grows, your Queries pane gets messy.

More files.
More tables.
More transformations.

And suddenly you’re scrolling endlessly trying to find the right table.

That’s not a data problem.

That’s an organization problem.

In this lesson, we clean up the Queries pane in Power BI so the project stays structured and professional.

Every dataset you import becomes a query.

Some of those queries will be used in analysis.
Some are just helper or staging tables.

If you don’t separate them, confusion builds fast.

Here’s what we do.

Create Query Groups

Instead of letting everything sit in one long list, we:

• Create a new group called Fact Tables

• Create another group called Source Data – Not for Analysis

This gives us a folder-like structure inside Power Query.

Move Tables into Groups

We:

• Move analysis-ready tables into Fact Tables

• Move intermediate or helper tables (like Products2, Products3, Products4) into Source Data

• Keep the workspace clean and logical

Now your Queries pane reflects business logic.

Fact tables = used in reporting
Source tables = used for transformation only

Instant clarity.

You’ll also notice something useful.

Power BI shows the number of queries inside each group.

So now you know exactly how many tables exist in your model.

Instead of guessing, you can confidently say:

“This project has 16 data sources.”

That’s real control.

Important Detail

The default “Other Queries” group cannot be deleted.
That’s normal. It’s built into Power BI.

If you’ve searched:

• How to group queries in Power BI

• Organize queries in Power Query

• Move queries to folder Power BI

• Best practice for managing tables in Power BI

• Difference between fact table and source table

This lesson gives you a practical structure you’ll use in bigger projects.

Because as we move into data modeling and dimension tables later, organization becomes even more critical.

Clean Queries pane.
Clear structure.
Professional workflow.

You’ve cleaned the data.

You’ve transformed it.

But here’s the mistake most people make next.

They jump straight into building visuals.

Without actually understanding the data.

Before you analyze, you profile.

Power BI gives you built-in tools for that inside Power Query. And they’re powerful.

Why Data Profiling Matters

You can’t answer questions like:

• Is this column reliable?

• Are there missing values?

• Is the salary data realistic?

• Are emails unique?

• Are there hidden errors?

Unless you explore the data first.

That’s where Column Profiling comes in.

1. Column Distribution

When you enable:

View → Column Distribution

Power BI shows:

• Distinct count

• Unique count

• Frequency of values

For example:

• 150 employees

• 150 unique email addresses → Good

• Reporting Manager column → Only 7 managers

• Employment Status → Active vs Inactive

You instantly understand structure.

No visuals needed.

2. Column Profile

Now enable:

View → Column Profile

This goes deeper.

For numeric columns like salary:

You get:

• Minimum value

• Maximum value

• Average

• Standard deviation

So now you know:

• Average salary ≈ $87,000–$88,000

• Maximum salary ≈ $149,000

That’s insight before building a single chart.

For categorical fields like Department:

You see:

• How many departments

• Distribution across departments

• Which department dominates

For Employment Status:

• 52% Active

• 48% Inactive

That’s something column distribution alone didn’t reveal.

3. Column Quality

Now the most important one.

Enable:

View → Column Quality

This shows:

• Valid values

• Errors

• Empty values

And here’s where we found a red flag.

In the Fulfillment table, the Delivery Ratings column had:

74% empty values.

That’s huge.

Now you have decisions to make:

• Remove the column?

• Validate with stakeholders?

• Fix missing data?

• Replace nulls?

Without profiling, you wouldn’t even know this was a problem.

What This Teaches You

Data transformation is mechanical.

Data profiling is analytical.

Before building dashboards, you should:

• Check uniqueness

• Check null percentages

• Check distribution

• Check min/max ranges

• Check data consistency

It prevents bad analysis later.

Numbers look clean.

Until they aren’t.

Decimals where they shouldn’t exist.
Financial values stored incorrectly.
Percentages that don’t behave like percentages.

If you don’t fix numerical data properly, your executive dashboard will show misleading insights.

In this lesson, we move into numerical data transformations in Power BI.

Because not every number should stay the way it is.

Rounding Values

Let’s take an example.

Your Employees table shows Annual Cost like:

83,848.62
91,204.29

In most business cases, companies don’t store payroll in random cents like that. You may want whole numbers.

Power BI gives you:

• Round Up

• Round Down

• Round

• Decimal control

Important best practice:

Instead of modifying the original column using Transform, use Add Column.

Why?

Because it preserves the raw data and creates a new calculated column.

So instead of overwriting Annual Cost, we create:

Employee Cost Annual Rounded

Now you have:

• Original value

• Rounded value

That’s data integrity.

Applying Contextual Judgment

Not every numeric column should be rounded.

For example:

• Product prices like 10.79 should remain exact

• Finance order values should not be altered

• Inventory purchase cost may need precision

Numerical transformation is contextual.

You don’t blindly round everything.

Applying Rounding Across Tables

We applied rounding where it made business sense:

• Asset Cost → Rounded

• Branch Investment Cost → Rounded

• Campaign Spend → Rounded

Each time:

Add Column → Round Up → Rename clearly

Then we moved into basic arithmetic transformations.

Creating Depreciation Calculations

In the Assets table:

• Asset Cost Final

• Depreciation Percent

We:

• Divided asset cost by depreciation percent

• Created Depreciated Value

• Subtracted depreciation from asset cost

• Rounded the final result

This demonstrates how to:

• Use Standard calculations

• Reference another column

• Create new calculated numeric fields

• Maintain clarity in naming

Power BI also supports:

• Square

• Square root

• Absolute value

• Statistical operations

• Aggregations

But again, apply them carefully.

If you’ve searched:

• How to round numbers in Power BI

• Round up vs round down Power BI

• Add calculated column in Power Query

• Numeric transformations in Power BI

• Calculate depreciation in Power BI

This lesson shows the fundamentals.

The key takeaway:

Never destroy original data unless absolutely necessary.

Create new columns.
Name them clearly.
Think about business logic before transforming numbers.

We will be answering the below business questions using calculated columns.

1. What is the full address of each branch by combining city, state, and zip code into one column?

2. Can we create a customer age group label (e.g., "18-25", "26-35") based on year of birth?

3. Can we flag each order as "High Value" or "Low Value" based on whether the order amount exceeds a threshold?

4. What is the profit margin per order by subtracting bundle price from the order amount?

5. Can we create a "New vs. Returning" label for each customer based on their signup date vs. first order date?

6. What is the asset age in years for each asset based on deployment date?

7. Can we create a delivery performance label — "Fast", "Average", or "Slow" — based on delivery start vs. end timestamp?

8. What is the total cost per inventory item by multiplying purchase cost by number of items in stock?

You made it.

That’s not small.

Finishing a course takes more than interest. It takes discipline. Most people start. Very few finish.

So first, respect.

Now let’s talk about what comes next.

Because this is not the end of your data journey. It’s the starting line.

Where You Go From Here

If you want to move from “I can build dashboards” to “I can drive decisions,” here’s what I strongly recommend learning next:

1. DAX (Data Analysis Expressions)

Power BI becomes truly powerful with DAX.

With DAX, you can:

• Build advanced calculations

• Create dynamic KPIs

• Handle time intelligence (YoY, MoM, YTD)

• Write logic that responds to filters

If dashboards are the body, DAX is the brain.

2. SQL

No serious data professional skips SQL.

SQL helps you:

• Extract data directly from databases

• Filter and join tables efficiently

• Understand how data is structured at the source

• Work with large datasets confidently

If you want to work with real company data, SQL is non-negotiable.

3. Data Storytelling

This is the skill most analysts ignore.

You can:

• Build the most beautiful dashboard

• Write the most complex DAX

• Optimize the cleanest model

But if you can’t explain what it means, it doesn’t matter.

Data storytelling helps you:

• Frame business problems

• Highlight insights clearly

• Drive action from analysis

• Present with confidence

This is what separates dashboard builders from strategic analysts.

What You’ve Already Built

You now understand:

• Data connections

• Power Query transformations

• Data profiling

• Merging and appending

• KPI building

• Dashboard formatting

• Insight-driven design

That’s a strong foundation.

Final Thought

Don’t let this be just another completed course.

Open Power BI.
Build something.
Break it.
Fix it.
Improve it.

That’s how you grow.

Thank you for committing to this journey.

Now go build dashboards that actually mean something.