DEV Community: Mercy-Shalom Adedayo

Data Preparation in Power BI: Cleaning, Transforming, and Loading Data

Mercy-Shalom Adedayo — Wed, 13 May 2026 15:59:27 +0000

The Real Work in Power BI Starts Before the Dashboard

Raw data is rarely ready for analysis straight out of the box. Blank rows, duplicate records, inconsistent date formats, merged cells, and poorly named columns are common in real-world datasets, and they cause real problems when ignored. Wrong totals, failed relationships, broken filters, and confusing visuals are not dashboard design failures. They are data preparation failures.

This is one of the most important things to understand about Power BI: the quality of your report depends entirely on the quality of the data behind it.

Power Query Editor is where that quality is built. It is Power BI's built-in data transformation tool, designed to help you clean, reshape, combine, and structure raw data before it ever loads into your data model. Rather than manually fixing spreadsheets every time new data arrives, Power Query lets you define your transformation steps once and apply them automatically on every refresh.

This guide walks you through practical Power Query techniques with clear steps and illustrations. Whether you are a beginner analyst, a business intelligence professional, or somewhere in between, these skills will help you work faster, reduce errors, and produce reports that are accurate and trustworthy.

Why Data Preparation Matters in Power BI

Before jumping into dashboards, your data should be:
• Accurate
• Complete
• Consistent
• Structured
• Ready for relationships and calculations

Poor data preparation often causes:

• Wrong totals
• Blank visuals
• Duplicate counts
• Slow reports
• Broken filters
• Misleading insights

Power Query helps solve these issues before they reach your dashboard.

1. Working with Power Query Editor

Power Query Editor is the data preparation engine inside Power BI. It is where raw, messy, and inconsistent data is transformed into a clean and structured dataset ready for analysis. Instead of manually editing files every time new data arrives, Power Query allows you to build repeatable transformation steps that run automatically whenever the data is refreshed.

How to Open It
• Open Power BI Desktop
• Click Home
• Select Transform Data
This launches Power Query Editor.

Things you can do in Power Query include:

• Connect to multiple data sources: Power BI can load data from multiple sources for report building. Getting data into Power BI from some sources is explained here.
• Clean messy data: Real-world data often contains issues like blank rows, duplicate records, null values, spelling inconsistencies, extra spaces, incorrect headers, etc. With Power Query, these can be fixed before loading into Power BI.
• Change data types: If data types are wrong, calculations and charts may fail. Power Query helps fix this early.
• Filter and sort data: This allows you to bring only the necessary data into Power BI, thereby improving the report performance.
• Split, merge and format columns
• Add custom and conditional columns
• Group and summarise data: Power Query can aggregate data before loading. This is useful when you need summary table.
• Combine multiple files in Power BI
• Track every change made to your file: Every action in Power Query is recorded under Applied Steps. This means you can make changes to any step later.
• Improve data quality with profiling tools: Power Query includes data profiling features like column quality, column distribution, and column profile. These give you a better grasp of your columns.
• Load clean data into Power BI: After all transformations are complete, click Close & Apply.

Main Sections to Understand
• Queries Pane – list of tables
• Data Preview – shows rows and columns
• Applied Steps – records every transformation
• Ribbon Menu – tools for cleaning and shaping data

2. Creating Index Columns

When working with datasets in Power BI, there are times when you need a unique number assigned to each row. This is where Index Columns become useful. An Index Column automatically adds sequential numbers to rows, helping you track records, preserve row order, create unique identifiers, or support advanced transformations. It is especially valuable when the original dataset has no ID column or when you need a simple ranking structure.

Why It Is Useful
• Create unique IDs
• Preserve original row order
• Build ranking logic
• Compare before/after transformations

Steps

Select your table
Go to Add Column
Click Index Column
Choose:
• From 0
• From 1
• Custom

3. Conditional Columns and Logic

Not all useful insights exist directly in raw data. Sometimes, you need to classify, label, or categorise records based on specific conditions. Conditional Columns in Power Query help you create new columns using logic such as If/Then/Else statements. This feature is useful for grouping customers using different parameters. Instead of creating formulas later in your report, you can build these logic-driven columns during data preparation, making your dataset cleaner, smarter, and easier to analyse once loaded into Power BI.

Steps

Go to Add Column
Select Conditional Column
Fill the columns according to the data logic in the Conditional Column window that opens
Click ok

4. Column from Examples

Many data transformation tasks involve extracting patterns from text, combining fields, or reformatting values. Instead of writing formulas manually, Power Query offers a smart feature called Column from Examples. This tool learns from examples you provide and automatically generates the transformation logic for the remaining rows. It is especially useful for splitting names, extracting codes, formatting dates, or creating custom text outputs. For users who may not know advanced formulas, this feature provides a fast and beginner-friendly way to perform intelligent transformations with minimal effort.

Steps

Add Column
Select Column from Examples
Enter sample outputs
Accept suggested transformation

Great For
• Extracting first names
• Splitting codes
• Formatting text
• Combining fields

5. Grouping and Aggregation

Grouping and Aggregation in Power Query help convert raw records into meaningful summaries such as total sales by region, average revenue by month, or customer counts by category. Instead of loading thousands of rows into Power BI and summarizing later, you can reduce complexity during preparation. This improves report performance and makes analysis easier. Grouping is particularly valuable when preparing management dashboards, financial summaries, or KPI reports where high-level insights are more important than row-level details.

Steps

Select column
Click Group By
Choose the aggregate to use for the data. That is, Sum, Count Rows, Average, Min, Max
Click ok

6. Pivoting and Unpivoting Columns

Pivoting and Unpivoting are transformation techniques used to reshape data into a more usable structure. Pivoting converts rows into columns, while Unpivoting turns multiple columns into rows. These features are extremely useful when working with monthly sales sheets, survey responses, or spreadsheet-style reports. By reshaping data properly in Power Query, you create cleaner tables that are easier to model, filter, and visualize in Power BI.

When to Use
• Pivot for matrix-style outputs
• Unpivot for dashboard-friendly structure
To pivot or unpivot columns, select the transform in the ribbon, in the “any column” section, select the desired option of choice (pivot or unpivot column option).

7. Merging and Appending Queries

Business data is often stored across multiple files, sheets, or systems rather than one single table. Power Query helps solve this by allowing you to combine datasets using Merge and Append operations. Merging joins related tables based on common keys, similar to SQL joins, while Appending stacks tables with similar structures into one larger dataset. These tools are essential when combining monthly reports, linking customers to transactions, or consolidating data from multiple departments. Proper use of Merge and Append helps create a unified dataset ready for accurate reporting and deeper analysis.

Merge Queries = Join Tables

Like SQL JOIN.

Append Queries = Stack Tables

Combine tables vertically.
Example:
• January Sales
• February Sales
• March Sales
Append into one Sales table.

Steps
Home → Merge Queries / Append Queries
You can merge or append into existing tables or as new. The options are available in the Merge and Append menu in Power Query.

8. Date and Time Transformations

Date and time fields are among the most valuable columns in business reporting because they help answer when events happened, how trends change over time, and what patterns exist seasonally. Power Query provides tools for transforming dates into useful parts such as year, quarter, month, week, and day name. It also helps correct inconsistent date formats and extract time components where needed. These transformations make time-based analysis much easier in Power BI, allowing users to build trend charts, monthly summaries, period comparisons, and forecasting reports more effectively.

Common Transformations
From a Date column, extract:
• Year
• Quarter
• Month
• Week
• Day Name
• Day Number

Steps

Select date column
Transform tab
Choose Date tools

9. Adding Prefix and Suffix Using Power Query

Sometimes raw data needs better formatting before it becomes presentation-ready. Adding Prefixes and Suffixes in Power Query is a simple but useful way to standardize values, improve readability, and align data with business naming conventions. For example, customer IDs can become CUST-1001, invoice numbers can include INV-, or amounts can display NGN after values. These small enhancements make datasets clearer and more professional. Instead of editing values manually, Power Query allows you to apply these formatting changes consistently across thousands of rows in seconds.
Useful for IDs and labels.

Steps

Select column
Transform
Format
Add Prefix / Add Suffix

10. Data Profiling Techniques

Before analyzing any dataset, it is important to understand its quality and structure. Power Query includes Data Profiling tools that help users inspect columns, detect issues, and understand patterns in the data. Features such as Column Quality, Column Distribution, and Column Profile reveal missing values, errors, duplicates, distinct counts, and statistical summaries. This allows analysts to identify problems early before they affect reports or dashboards. Data profiling is a critical step in professional analytics because it ensures that decisions are based on reliable, complete, and trustworthy data.
Enable from View Tab

Turn on:
• Column Quality
• Column Distribution
• Column Profile

What They Show
Column Quality
• Valid values
• Errors
• Empty values

Column Distribution
• Distinct values
• Frequency patterns

Column Profile
Detailed stats:
• Min
• Max
• Average
• Count
• Errors

Why It Matters
You can detect issues before loading data.

11. Handling Missing or Null Values

Missing values are common in real-world datasets and can create serious issues if ignored. Blank customer names, null sales amounts, or incomplete dates may lead to wrong calculations, broken visuals, or misleading insights. Power Query provides tools to handle these problems efficiently by replacing null values, removing blank rows, filling missing entries, or deleting duplicates. Cleaning missing data during preparation ensures a stronger and more accurate dataset before it reaches the reporting stage. Proper handling of null values is one of the most important habits every Power BI user should develop.
Replace Nulls
Example:
Null sales values → 0

Steps:

Select column
Replace Values
Replace null with 0
Click ok

Remove Blank Rows
Home → Remove Rows → Remove Blank Rows
Remove Duplicates
Select key column(s) → Remove Rows → Remove Duplicates

12. Load Clean Data into Power BI

After transformations:
Click Close & Apply

Power BI loads cleaned data model. You then start building relationships, measures, and visuals.

Conclusion

Clean Data Is Your Superpower

You've just covered the core of what professional Power BI analysts do before they ever touch a chart, and that puts you ahead of most beginners who dive straight into visuals and wonder why things fall apart.

These aren't just technical steps; they're habits. Every great analyst you've ever admired builds these checks and transformations into their workflow because they know that trustworthy insights start with trustworthy data.

The good news? You don't have to master everything at once. Start with one technique, apply it to a real dataset you're working with, and watch how much easier the rest of your analysis becomes. Come back to this guide whenever you need a refresher; that's exactly what it's here for.

Now go open Power Query Editor and start turning messy data into something remarkable.

8 Excel Functions Every Professional Should Know (With Real Use Cases)

Mercy-Shalom Adedayo — Wed, 15 Apr 2026 13:45:30 +0000

Spreadsheets are everywhere; finance teams, HR departments, product managers, marketers, and solo freelancers all rely on them. But most professionals use Excel at only a fraction of its potential, manually repeating work that a single formula could handle in seconds.
This article isn't about becoming an Excel expert. It's about knowing the eight functions that cover roughly 80% of everyday professional spreadsheet tasks, the ones that reduce errors, speed up reporting, and free your brain for actual decision-making.
Each function below includes a plain-language explanation, a working formula example, and a grounded, real-world use case. Whether you work in sales, operations, HR, or anywhere in between, at least a few of these will change how you work.

1. SUM()

Quickly calculate totals. Add any range of numbers instantly.

What it does
Adds all numeric values within a specified cell range. One of Excel's most fundamental functions, and the most used.
Syntax

=SUM(B2:B31)

Real-world use case
You manage a retail store and track daily sales in column B. Rather than manually summing 30+ rows on a calculator, =SUM() gives you total monthly revenue instantly. Add a new row for today's sales? The total updates on its own; no recalculation is needed.

Pro tip
You can also use =SUM(B2:B31, D2:D31) to add across multiple non-contiguous ranges in a single formula.

2. AVERAGE()

Measure typical performance. It finds the mean of any numeric range.

What it does
Returns the arithmetic mean of a range: the sum divided by the count of values. Essential for benchmarking and trend awareness.
Syntax

=AVERAGE(B2:B31)

Real-world use case
As a team lead reviewing monthly performance, you want a baseline, not just totals. =AVERAGE() reveals the typical daily output, helping you distinguish a strong week from an outlier day. It's the foundation of setting realistic performance targets.

3. IF()

Automate decisions. It applies logic without manual review.

What it does
Tests a condition and returns one result if true, another if false.The backbone of conditional logic in any spreadsheet.
Syntax

=IF(C2>=100000, "Target Met", "Below Target")

Real-world use case
You have a sales report with 50 reps. Rather than manually reading each row to flag who hit their quota, =IF() automatically labels every entry as "Target Met" or "Below Target." This feeds directly into dashboards and management summaries without any manual input.
Pro Tip:
Nest multiple IF statements, or use =IFS() to handle more than two outcomes, e.g., "Exceeded", "Met", "Below", "Critical".

4. VLOOKUP()

Retrieve data from tables. It searches and pulls matching records.

What it does
Searches for a value in the first column of a range and returns a value in the same row from a specified column. Replaces manual lookups entirely.
Syntax

=VLOOKUP(A2, ProductTable, 3, FALSE)

Real-world use case
You have an order sheet with product IDs and a separate pricing table. Instead of manually cross-referencing hundreds of rows, =VLOOKUP fetches the correct price for each ID automatically, cutting hours of work down to seconds and eliminating copy-paste errors.
Modern alternative
If you're on Excel 365 or 2019+, consider =XLOOKUP(); it's more flexible, doesn't require the lookup value to be in the first column, and handles errors more gracefully.

5. CONCAT()

Combine text fields. Merge multiple cells into one clean string.

What it does
Joins text from multiple cells into a single string. Great for combining names, IDs, addresses, emails, or any label-based data that comes split across columns.
Syntax

=CONCAT(A2, " ", B2)

Real-world use case
Your CRM export has "First Name" and "Last Name" in separate columns. Before sending a mail merge or generating a report, =CONCAT()automatically stitches them into full names, making the dataset clean and presentation-ready without manual editing.
Also useful
=TEXTJOIN(", ", TRUE, A2:A10) is powerful when you need to merge a whole range with a custom separator and skip blank cells automatically.

6. COUNT() / COUNTA()

Track data entries. Know exactly how many records you have.

What they do
COUNT tallies only numeric cells. COUNTA counts all non-empty cells — numbers, text, dates, and everything in between.
Syntax

=COUNT(A2:A100)
=COUNTA(B2:B100)

Real-world use case
You're managing a survey sheet that fills over time. =COUNTA() on the respondent column instantly tells you how many submissions you've received, useful for tracking response rates in real time without scrolling to the last row.

Quick comparison

| Function     | What It Counts     |
|--------------|--------------------|
| COUNT        | Numeric values     |
| COUNTA       | Non-empty cells    |
| COUNTBLANK   | Empty cells        |

7. TODAY()

Work with live dates. Keep time-sensitive data always current.

What it does
Returns today's date and updates automatically every time the workbook is opened. No manual date entry ever.
Syntax

=TODAY()
=TODAY()-A2
=A2-TODAY()

Real-world use case
In an accounts receivable sheet, you can calculate how many days overdue each invoice is using =TODAY()-InvoiceDate. The aging calculation stays accurate every time the file is opened, so you never need to manually refresh due-date columns.
Related functions
Use =NOW() to capture the current date and time. Use =DATEDIF(start, end, "d") for more complex date difference calculations (days, months, or years apart).

8. MAX() / MIN()

Identify key metrics. Pinpoint peaks and floors instantly.

What they do
MAX returns the highest value in a range. MIN returns the lowest. Useful for spotting outliers, records, or performance floors.
Syntax

=MAX(B2:B31)
=MIN(B2:B31)

Real-world use case
You have 300 rows of daily sales data. Rather than visually scanning hundreds of figures to find the best and worst days, =MAX() and =MIN() extract them instantly. Pair with =MATCH() to also return the date of the peak, making performance analysis genuinely actionable.

Quick reference

| Function            | Purpose                    | Best Use Cases                         |
|---------------------|----------------------------|----------------------------------|
| SUM()               | Add a range of numbers     | Totals, revenue, budgets         |
| AVERAGE()           | Find the mean              | Benchmarks, performance tracking |
| IF()                | Conditional logic          | Flags, labels, dashboards        |
| VLOOKUP()           | Cross-table lookups        | Pricing, rosters, catalogs       |
| CONCAT()            | Merge text strings         | Names, IDs, mail merge prep      |
| COUNT / COUNTA()    | Count entries              | Surveys, records, completeness   |
| TODAY()             | Live current date          | Aging, deadlines, scheduling     |
| MAX / MIN()         | Highest/lowest value       | Performance, outliers, records   |

Building efficiency, one formula at a time

These eight functions aren't just shortcuts; they represent a shift in how you interact with data. The difference between a professional who scrolls through spreadsheets hunting for answers and one who has them surface automatically is usually a handful of well-placed formulas.
"You don't need to master all of Excel to be effective. You need to master the parts that touch your work every day."
Start by picking two or three from this list that apply directly to your current projects. Use them consistently for a week. The muscle memory builds quickly, and so does the time saved.
As your comfort grows, these functions also serve as building blocks for more advanced patterns: combining IF with VLOOKUP, using TODAY inside conditional formatting, or nesting MAX with MATCH to find not just the top value but exactly where it lives in your data.
Efficiency in spreadsheets isn't built in a day. It's built one applied formula at a time, and today is a perfectly good day to start.

#excel #productivity #spreadsheets #dataanalysis #tutorial #beginners #officeskills

How to Get Data Into Power BI as a Newbie

Mercy-Shalom Adedayo — Sat, 04 Apr 2026 22:59:22 +0000

Introduction

In today's world, companies have data everywhere. Excel sheets, company databases, cloud apps, and websites. As a beginner data analyst, your first big skill is grabbing all that data and putting it in one place. Power BI makes this super easy with its simple tools.

Power BI is like a magic dashboard maker. It connects to tons of data spots, cleans up the messy info, and turns it into cool charts and reports. Whether it's a basic spreadsheet or fancy online storage, Power BI pulls everything together so you can see the full story.

The hero behind this is Power Query. Think of it as your data cleaner and organiser. It does ETL: Extract (grab the data), Transform (fix and reshape it), and Load (put it into Power BI for analysis). No coding needed, just clicks!

Power BI connects to all these common sources:
• Files like Excel (.xlsx) or CSV.
• Databases like SQL Server (your company's main data storage).
• Cloud stuff like Google Sheets or SharePoint.
• Websites and APIs (live data from the web).

It has ready-made "connectors" for over 100 sources. You pick one, sign in if needed, and choose Import (copies data in) or Direct Query (keeps it live and fresh).

Microsoft teaches this in their free training: Always check your sources first, pick the right way to connect, and make loading fast. Data isn't neat in one file, it's scattered, so mixing it right is key to good reports.
This blog walks you through it step-by-step:
• How to click "Get Data" and pick sources.
• Real examples with Excel, SQL, and web data.
• Tips to link everything without headaches.

By the end, you'll confidently pull data from anywhere in Power BI. No more data chaos, just clear insights!

Connecting Data from Multiple Sources

Power BI allows you to connect to a wide range of data sources. Below are step-by-step guides for each major source.

Step 1: Connecting to Excel

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

Step 2: Connecting to Text/CSV Files

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

Step 3: Connecting to PDF

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

Step 4: Connecting to JSON

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

Step 5: Connecting to SharePoint Folder

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

Step 6: Connecting to MySQL Database

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

Step 7: Connecting to SQL Server

Open Power BI Desktop
Navigate to Home → Get Data → SQL Server
Enter the server name
Leave the database field blank (or specify one if needed)
Click OK
Select authentication method
In the Navigator pane, expand the database
Select required tables
Click Transform Data to open Power Query Editor

Step 8: Connecting to Azure Analysis Services

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

Conclusion

Connecting to multiple data sources in Power BI is a key step in building reliable and meaningful reports. Data often comes from different places, such as files, databases, and cloud services, and Power BI makes it easy to bring everything together in one environment.
With tools like Power Query, you can not only connect to data but also clean and transform it into a structured format ready for analysis. This preparation stage is essential, as well-organised data leads to more accurate insights and better decision-making.
For beginners, learning to connect to and prepare data properly lays a strong foundation for creating effective dashboards. Ultimately, successful data analysis in Power BI starts with how well you gather, clean, and combine your data from different sources.