Forem: Bamgboye Simisola

Mastering Power Query in Power BI: A Complete Data Transformation Guide

Bamgboye Simisola — Sun, 10 May 2026 06:16:40 +0000

Power Query is the backbone of data preparation in Power BI. Before you can build stunning dashboards or write complex DAX, your data needs to be clean, consistently shaped, and properly related. In this guide, we'll use the real CodeSphere Hub dataset, comprising sales transactions, booking records, product info, and calendar data, to demonstrate every essential Power Query technique.

The dataset includes 163,000+ sales rows spanning 2015–2020, a messy hotel bookings CSV full of nulls and inconsistencies, a product catalogue, and several lookup tables. Exactly the kind of real-world mess Power Query was built to handle.

Working with Power Query Editor

The Power Query Editor is where all data transformation happens before data is loaded into Power BI's model. You access it by selecting Transform Data after importing a source, or from the Home ribbon in Power BI Desktop.

Key Interface Areas

Query Panel (left): Lists all loaded tables. Our project has 12 queries including Sales, Data Cleaning, Calendar, and all lookup tables.

Data Preview (center): Shows a sample of your data. Columns are typed (date, text, number) and clickable for column-level operations.

Applied Steps (right): Every action you take is recorded here as a step you can edit, reorder, or delete. This is Power Query's version of version control.

Formula Bar: Shows the M code behind each step, toggle it on from the View tab.

Data Profiling Techniques

Before cleaning, you need to understand your data. Power Query has three built-in profiling views that surface quality issues instantly. Open them from View → Column Quality, Column Distribution, Column Profile.

Column Quality
Shows three metrics as a percentage bar at the top of each column: Valid (green), Error (red), and Empty/Null (gray). In the Data Cleaning table, you can immediately see that guest_name and payment_status have significant null rates.

Column Distribution
Shows value frequency as a histogram under each column. Instantly reveals skewed distributions, outlier spikes, or unexpected cardinalities. For example, the listing_city column's distribution would show the "LA"/"los angeles"/"Los Angeles" fragmentation.

Column Profile
The most detailed view — select a single column and see stats panel: min, max, average, count, distinct count, null count, and a value frequency chart. Available for the entire dataset (not just top 1,000 rows) when you change the profiling scope at the bottom status bar.

Handling Missing and Null Values

The Data Cleaning table is a goldmine of real-world data quality issues — nulls in guest_name and payment_status, negative nights values, inconsistent boolean representations (True/False/Yes/No/n), and extra whitespace in guest names. Let's tackle each systematically.

1. Removing Rows with Nulls
For columns where a null makes a row useless (e.g., missing booking_id), remove the row entirely.

2. Replacing Nulls with Default Values
For optional fields, replace nulls with a meaningful default rather than deleting the row.
Default values
Replace with 0, mean or median for numerical fields
Replace with “Unknown” for categorical fields
Replace with business-defined defaults

Select the column
Right-click
Choose Replace Values

3. Removing Duplicates
Power Query can deduplicate on one or multiple key columns. For booking data, booking_id should be unique.

Select the key column(s)
— e.g., booking_id.
Home → Remove Rows → Remove Duplicates
(or right-click the column → Remove Duplicates).

OR right-click the column → Remove Duplicates

Creating Index Columns

Index columns provide unique row identifiers, essential for building relationships, tracking row positions, and creating surrogate keys. Our CodeSphere Hub Sales table doesn't have a guaranteed unique row key, making an index column critical.

Open the target query Select Fact Table in the Queries panel on the left.
Navigate to Add Column → Index Column In the ribbon, click Add Column tab, then click the dropdown arrow on Index Column.
Choose your start value Select From 1 (1-based) for human-readable IDs, or From 0 for zero-based indexing used in joins.
Rename the column Double-click the new Index column header and rename it.

Conditional Columns and Logic

Conditional columns let you create new derived columns based on if/else logic, without writing complex DAX measures. In the Data Cleaning table, we can classify bookings by payment status and validity.

Go to Add Column → Conditional Column
Set up the first condition
Add an else-if clause
Set the else (default) value

Column from Examples

This is one of Power Query's most powerful AI-assisted features. You provide 1–3 example output values, and Power Query infers the transformation rule automatically. It's ideal when you know what output you want but don't know the exact M formula.

Select column
Click on the column header to highlight it.
Add Column → Column from Examples → From Selection
A new editable column panel appears on the right.
Type your example output in the first cell
For example, "los angeles" type Los Angeles, for "LA" type Los Angeles, for "San Fran" type San Francisco.
Press Enter after each example
Power Query suggests a formula. Keep typing examples until the entire column looks correct. Click OK.

Grouping and Aggregation

Group By is Power Query's equivalent of SQL's GROUP BY; it lets you summarise data by collapsing rows into aggregated results. This is useful before loading data, reducing model size and query load time.
Common aggregation functions include:
Sum
Count
Average
Minimum
Maximum

Select the Sales query
Make sure you're working on the right table.
Home → Group By
The Group By dialogue opens.
Configure grouping
Group By column: Region_Key. New column name: TotalQty. Operation: Sum. Column: Quantity_Sold.
Add a second aggregation (optional)
Click Add aggregation. New column: TotalTransactions. Operation: Count Rows.

Pivoting and Unpivoting Columns

The Pivot Demo.csv in our dataset is a perfect real-world example; it contains years as column headers (2015, 2016, 2017…) with metrics as rows. This "wide" format is common in exports but difficult to work with in Power BI's data model, which needs a "long" format.

Unpivoting: Wide → Long (Most Common)

Load the Pivot Demo table
It has columns: Metric, 2015, 2016, 2017, 2018, 2019, 2020.
Select the year columns (2015–2020)
Hold Ctrl and click each year column header.
Transform → Unpivot Columns
Power Query creates an Attribute column (the year) and a Value column (the number).
Rename columns
Rename Attribute to Year and Value to Amount.

Merging and Appending Queries

When a dataset has two separate annual sales files, we have to look up tables (Regions, Gender, Categories) that need to be joined.

Appending Queries (Union / Stack Rows)

Use Append when you want to combine tables with the same structure — like stacking 2019 and 2020 sales into one table.

Home → Append Queries → Append Queries as New
(creates a new query instead of modifying an existing one)
Select tables to combine.
Choose three or more tables and add Sales 2019 and Sales 2020 to the list.
Click OK.
Power Query stacks the rows. Columns are matched by name; mismatches produce nulls.

Merging Queries (SQL-style JOIN)

Use Merge to bring lookup columns into your fact table. Let's enrich our Sales table with Region names from the Regions lookup.

Select Sales query → Home → Merge Queries
Select the right table:
Choose Regions. Click Region_Key in both tables to set the join key.
Join Kind: Select Left Outer (all Sales rows + matching Region info).
Expand the nested table column.
Click the expand icon on the new Regions column. Uncheck all but Region and Country. Uncheck "Use original column name as prefix."

Date and Time Transformations

The Calendar table contains date-time strings in ISO format, like 2015-01-01T09:53:24. Proper date handling is critical for time intelligence in DAX.

Key Date Operations

Change Type to Date: Select Date_and_Time column → Transform → Data Type → Date/Time → Date.
Extract Year/Month/Day: Add Column → Date → Year / Month / Day. Creates integer columns useful for slicers.
Extract Month Name: Add Column → Date → Month → Name of Month. Returns "January", "February", etc.
Extract Day of Week: Add Column → Date → Day → Day of Week. Returns 0 (Sunday) through 6 (Saturday) — or Name of Day for text.
Calculate Date Difference: Use a Custom Column with an M formula to find the duration between dates.

Adding Prefix and Suffix Using Power Query

Adding prefixes or suffixes to values standardises identifiers, helps distinguish keys across tables, and makes data more readable. Our Invoice_ID column already uses "INV-" prefixes — let's see how that's done and how to apply it to other columns.

Format → Add Prefix/Suffix (GUI)

Select the column
(e.g., Region_Key)
Transform tab → Format → Add Prefix
Type the prefix, e.g., REG-. Result: 1 → REG-1, 10 → REG-10.

You Now Have a Clean, Model-Ready Dataset

Working through all these techniques on any dataset, you've transformed a collection of raw, messy CSVs into a structured, consistent, analysis-ready data model. Here's what was accomplished:

Loaded different tables and datasets
Added index columns as surrogate keys for relationship management
Created conditional columns to flag data quality issues and categorise data
Unpivoted the wide Pivot Demo table into a proper long format
Merged sales data with Region and many more datasets
Appended 2019 and 2020 sales files into a unified table
Extracted date parts from the Calendar table for time intelligence
Identified and resolved 7 data quality issues in the booking dataset

With this foundation in Power Query, your DAX measures will be simpler, your reports faster, and your insights more reliable. Happy modelling!

Getting Data from Multiple Sources in Power BI

Bamgboye Simisola — Sat, 04 Apr 2026 00:31:26 +0000

Introduction

Let’s be honest, Power BI dashboards can look really pretty. But if the data behind them is messy, incomplete, or just plain confusing, then congratulations… you’ve built a very attractive lie.

At the heart of every solid Power BI report is one thing: good data coming in the right way.

In the real world, your data is never sitting nicely in one place waiting for you. Nope. It’s scattered everywhere, Excel files from one department, CSVs from another, a database somewhere, maybe even a random PDF someone swears is “the source of truth.”

This is where Power BI earns its paycheck.

With its Get Data feature and Power Query, you can pull in data from multiple sources, clean it up, and actually make sense of it, all in one place.

In this guide, we’ll walk through how to:

Connect Power BI to different types of data sources without stress
Use Power Query to preview and understand what you’re working with
Catch data issues early (before they embarrass you later)
Set up a clean foundation for proper analysis and reporting.

Architecture Overview

Before we start clicking buttons, let’s quickly understand what’s going on behind the scenes.

Think of Power BI as the central hub where all your scattered data finally comes together to behave.

Here’s the simple breakdown:

Power BI Desktop → where you build your reports and models
Data Sources → where your data lives (and misbehaves), such as:
Excel and CSV files
SQL databases
JSON files and APIs
PDFs (yes, even those stubborn ones)
SharePoint folders

All this data flows into Power Query, which is basically your “data cleaning assistant.”

This is where you:

Preview your data
Fix errors
Transform messy columns
And make everything analysis-ready

Only after that does the data get loaded into your model, where the real magic (and dashboards) happen.

Before we start clicking buttons, let’s quickly understand what’s going on behind the scenes.

Think of Power BI as the central hub where all your scattered data finally comes together to behave.

Connecting Data from Multiple Sources

Now to the real work, actually getting the data into Power BI.

Power BI connects to many data sources, which is great… until you realise you might need to connect to all of them in a single project. Don’t worry, once you understand the pattern, it becomes pretty straightforward.

Let’s walk through the common ones you’ll definitely run into:

Step 1: Connecting to Excel

This is usually where everyone starts nice, familiar, and (mostly) well-behaved.

Open Power BI Desktop
Go to Home → Get Data → Excel

Select your Excel file

In the Navigator, choose the sheet or table you need

Click Load or Transform Data

If the Excel file is messy (and it usually is), just go straight to Transform Data and fix it before loading. Save yourself future stress.

Step 2: Connecting to Text/CSV Files

CSV files look simple… until you open them and everything is in one column.

Go to Home → Get Data → Text/CSV

Select your file

Preview the data

Click Load or Transform Data

Always preview properly here—wrong delimiters can ruin your day.

Step 3: Connecting to PDF

Yes, Power BI can pull tables from PDFs. And yes, it feels a bit like magic.

Go to Home → Get Data → PDF

Select your file

Choose what you need

Click Load or Transform Data

Just a heads-up: not all PDFs behave nicely, so expect to do some cleaning.

Step 4: Connecting to JSON

JSON is common when working with APIs, and it can look… intimidating at first.

Go to Home → Get Data → JSON

Select your file or API endpoint

Load into Power Query

Expand nested fields to make the data readable
Click Close & Apply

The key here is mastering the “expand” feature—otherwise, you’ll just be staring at records inside records forever.

Step 5: Connecting to SharePoint Folder

Perfect for team environments where files live in the cloud.

Go to Home → Get Data → SharePoint Folder

Enter the SharePoint URL

Authenticate if needed

Select your files
Click Combine & Transform Data

This is super useful when files are updated regularly—you don’t have to reconnect every time.

Step 6: Connecting to MySQL Database

Now we’re stepping into database territory.

Go to Home → Get Data → MySQL Database

Enter server and database details

Provide credentials
Select the tables you need
Click Load or Transform Data

At this point, you’re not just importing data—you’re choosing what actually matters.

Step 7: Connecting to SQL Server

Very similar to MySQL, just a bit more common in enterprise setups.

Go to Home → Get Data → SQL Server

Enter the server name

Choose authentication

Select your database and tables

Click Transform Data

This is where your SQL knowledge really starts to shine, especially if you decide to write queries instead of loading full tables.

Step 8: Connecting to Web Data

For pulling data directly from websites or APIs.

Go to Home → Get Data → Web

Enter the URL

Select the detected data

Click Load or Transform Data

Very useful, but also where things can break easily if the source structure changes.

Step 9: Connecting to Azure Analysis Services

This is more on the advanced/enterprise side.

Go to Home → Get Data → Azure → Azure Analysis Services

Enter the server

Select your model
Choose Live Connection
Click Connect

Here, you’re not importing data, you’re connecting directly to an existing model.

If you’re noticing a pattern, you’re right:
Connect → Preview → Transform → Load

Once you get comfortable with that flow, switching between data sources becomes second nature.

Conclusion

Connecting to multiple data sources in Power BI isn’t just a setup step, it’s where everything begins.

Because the truth is, your dashboard is only as good as the data behind it. And in real-life scenarios, that data is coming from different places, in different formats, and sometimes in very questionable conditions.

Power BI makes this process manageable, especially with Power Query acting as your cleanup zone. It gives you the chance to actually understand your data before jumping into visuals.

But here’s the important part:
connecting data is easy; preparing it properly is where the real skill is.

When you take the time to:

catch inconsistencies early
handle missing or messy values
and shape your data properly

you end up with reports that people can actually trust.

And that’s the goal, not just pretty dashboards, but reliable insights.

At the end of the day, good analysis doesn’t start with charts.
It starts with how well you bring your data together in the first place.