Module 6: Data Transformation Using Power Query Editor

Power BI Module 6: Data Transformation – Group, Pivot, and Custom Columns

Raw data is rarely ready for reporting. It often needs cleaning, reshaping, and enhancement. Power BI’s Power Query Editor makes this process easy with a no-code interface for data transformation.

📌 What is Data Transformation?

Data transformation is the process of converting messy, disorganized, or repetitive data into clean, structured formats suitable for analysis. Power BI’s transformation logic is stored as “steps” in the Applied Steps pane.

• Go to Home → Transform Data to open Power Query Editor
• Use UI tools or write custom M code to shape your data
• All steps are saved and auto-applied on refresh

🧮 Group Rows (Summarize Data)

Grouping allows you to create summaries like total sales by region, average ratings by product, or count of customers by city.

• Select the column(s) you want to group by (e.g., Region)
• Click Transform → Group By
• Choose aggregation: Sum, Count, Average, Min, Max, etc.

Example: Group a sales table by Salesperson and aggregate Revenue using “Sum” to get total revenue per person.

📌 Advanced Grouping:

• Add multiple group levels (e.g., Region + Product)
• Create multiple aggregations at once (e.g., total sales and average discount)

🔁 Pivot Columns (Convert Rows to Columns)

Pivoting transforms row values into column headers — useful when you want to see months as columns or product categories across the top.

• Select the column to pivot (e.g., Month)
• Click Transform → Pivot Column
• Select a values column (e.g., Sales) to fill the pivoted structure

Use Case: You have a table with Month and Sales. Pivot Month to see each month as a separate column with corresponding sales values.

⚠️ Tip:

Before pivoting, ensure that row combinations are unique (e.g., one value per month per region). If not, use aggregations like Sum or Average.

➕ Create Custom Columns

Custom columns let you add new logic or calculated values using formulas. It’s like creating a new column in Excel.

• Click Add Column → Custom Column
• Write a formula using fields and M syntax (e.g., [Revenue] - [Cost])
• Give the column a name (e.g., Profit) and click OK

Examples:

• If [Region] = "North" then [Sales] * 1.1 else [Sales]
• [FirstName] & " " & [LastName] to combine names
• Date.Year([OrderDate]) to extract the year

📌 Use Case: Create a “Profit Category” column that labels each row as “High”, “Medium”, or “Low” based on profit value.

🧠 Pro Tips for Efficient Data Shaping

• Always remove unnecessary columns before applying transformations
• Rename applied steps to keep the transformation flow readable
• Use Advanced Editor to edit M code directly for complex logic
• Preview your data after every transformation step

📌 Summary – Clean Data = Accurate Reports

• Group Rows for rollups and summaries
• Pivot Columns for transforming structure
• Custom Columns for adding calculated logic
• Use Power Query before loading into visuals – clean once, reuse always

🎯 Final Thought: Well-structured data is the secret to powerful visuals and fast dashboards. Power Query makes it easy to automate data prep and focus on insights.