---

🔄 Power Query – Data Transformation in Power BI (with Example)

📅 Updated: June 2025
✍️ By TechTown.in
🔍 Keywords: Power Query, Data Transformation, Power BI, Load vs Transform, CSV to Table, ETL in Power BI, Power BI CSV Import

Sample file :

---

🔰 What is Power Query?

Power Query is Power BI’s built-in data transformation engine. It allows you to take raw data from different sources — like Excel, CSV, or databases — and clean, shape, and prepare it for powerful visual analysis.

You don’t need to be a coder or data scientist. With point-and-click tools, Power Query lets you:

• Remove errors
• Fix column types
• Split/merge columns
• Create calculated fields
• Apply conditional logic
• And much more!

It’s where ETL (Extract, Transform, Load) happens in Power BI.

---

📥 Starting with Data Import – The Example CSV

Let’s say we import a file called:
📁 techtown_sales_data_jan2025.csv

When loaded into Power BI Desktop, you first land in the Navigator screen (as shown in your image). This gives you a snapshot of the data and a choice:

🟢 Load

• Loads the data as-is directly into Power BI.
• No transformation — just straight into the data model.
• Useful if the dataset is already clean.

🛠️ Transform Data

• Opens the Power Query Editor.
• Allows you to edit, clean, and reshape the data.
• Best for real-world messy datasets.

✅ Pro Tip: Always click on Transform Data if you’re unsure about data quality.

---

🧠 Understanding the Dataset

Here’s a quick look at the columns we’re working with:

Column	Description
Date	Order date
OrderID	Unique order identifier
CustomerID	Customer code
CustomerName	Customer full name
ProductCategory	Type of product
ProductName	Specific product name
Quantity	Units purchased
UnitPrice	Price per unit (₹)
TotalSale	Quantity × UnitPrice
StoreRegion	Regional store location

---

🔄 What is Data Transformation?

Data transformation means converting raw data into clean, structured, and meaningful data.

Using Power Query, here are some transformations you might perform:

✅ Common Transformations:

Task	Power Query Feature
Remove unwanted columns	Home → Remove Columns
Fix data types (e.g. date/text)	Transform → Data Type
Fill missing values	Transform → Fill → Down/Up
Remove duplicates	Home → Remove Rows → Remove Duplicates
Add new calculations	Add Column → Custom Column
Split full names	Split Column → By Delimiter
Rename columns	Right-click → Rename
Combine columns	Transform → Merge Columns
Filter records	Filter icon on column headers

---

🛠️ Real-Life Example: Steps in Power Query

When we hit “Transform Data”, Power Query Editor opens. Here’s a sample transformation flow for this dataset:

1. Promote Headers
   ➤ Automatically uses first row as column headers
   Step: Promoted Headers
2. Change Data Types
   ➤ Make sure Date is Date, Quantity is Whole Number, UnitPrice is Decimal, etc.
   Step: Changed Type
3. Add a Custom Column
   ➤ Recalculate TotalSale = Quantity * UnitPrice if not already present
   Add Column → Custom Column → [Quantity] * [UnitPrice]
4. Remove Unwanted Columns
   ➤ Say you don’t need CustomerID, you can remove it
   Home → Remove Columns
5. Split Full Name (Optional)
   ➤ CustomerName → Split Column → By Space → First Name, Last Name

---

📊 Output: Cleaned & Ready Data

After transformation, your dataset is:

• Structured
• Properly typed
• Filtered
• Enriched with calculated fields

Then, click on Close & Apply to load this data into Power BI’s Data Model — ready for reports, visuals, and dashboards.

---

⚙️ Load vs Transform – What Should You Choose?

Option	When to Use
Load	If the data is already clean and formatted
Transform	If the data is messy, needs calculations, filters, cleanup

✅ Best Practice: Always review in Power Query Editor — even a clean CSV can have wrong data types or NULLs.

---

🔗 Related Features in Power Query

Feature	Use Case
Column Quality	Visual indicators of errors, nulls, valid values
Column Distribution	Frequency charts of column values
Merge Queries	Join data from two different sources
Append Queries	Stack data from multiple files/tables
Group By	Create summarized tables (e.g., total sale by region)
Parameters	Reusable dynamic values (like Date filters)

---

🧠 Power Tips for Beginners

• Enable Column Profiling via the View tab to see column statistics
• Use Remove Errors to clean broken rows
• Always check data types before loading
• Use Advanced Editor to view underlying M code for each step
• Every transformation is recorded and repeatable — ideal for automated reports

---

📚 Conclusion

Power Query isn’t just a tool — it’s your first line of defense in building clean, trusted data models in Power BI.

With its no-code interface and powerful transformation capabilities, Power Query empowers analysts, students, and professionals to prepare data like a pro.

🌟 If you clean data well, your insights become powerful.
Begin every Power BI project in Power Query Editor — and you’re already 50% done!

---