🔄 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:
- Promote Headers
➤ Automatically uses first row as column headersStep: Promoted Headers - Change Data Types
➤ Make sure Date isDate, Quantity isWhole Number, UnitPrice isDecimal, etc.Step: Changed Type - Add a Custom Column
➤ RecalculateTotalSale = Quantity * UnitPriceif not already presentAdd Column → Custom Column → [Quantity] * [UnitPrice] - Remove Unwanted Columns
➤ Say you don’t needCustomerID, you can remove itHome → Remove Columns - 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
Viewtab 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!