🔄 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 :

techtown_sales_dataDownload

🔰 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:

ColumnDescription
DateOrder date
OrderIDUnique order identifier
CustomerIDCustomer code
CustomerNameCustomer full name
ProductCategoryType of product
ProductNameSpecific product name
QuantityUnits purchased
UnitPricePrice per unit (₹)
TotalSaleQuantity × UnitPrice
StoreRegionRegional 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:

TaskPower Query Feature
Remove unwanted columnsHome → Remove Columns
Fix data types (e.g. date/text)Transform → Data Type
Fill missing valuesTransform → Fill → Down/Up
Remove duplicatesHome → Remove Rows → Remove Duplicates
Add new calculationsAdd Column → Custom Column
Split full namesSplit Column → By Delimiter
Rename columnsRight-click → Rename
Combine columnsTransform → Merge Columns
Filter recordsFilter 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?

OptionWhen to Use
LoadIf the data is already clean and formatted
TransformIf 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

FeatureUse Case
Column QualityVisual indicators of errors, nulls, valid values
Column DistributionFrequency charts of column values
Merge QueriesJoin data from two different sources
Append QueriesStack data from multiple files/tables
Group ByCreate summarized tables (e.g., total sale by region)
ParametersReusable 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!