🔧 Power Query Data Cleaning & Transformation Methods 1.
Remove Duplicates Use Case:
Remove duplicate Shipment IDs or customer records. Steps: Select the column(s) Click on
“Remove Duplicates” in the ribbon. --- 2. Remove Blank/Null Rows Use Case: Eliminate rows
with missing Email, Phone Number, or Shipping Address. Steps: Select column → Filter out null
or blank OR use “Remove Empty” from the Home > Remove Rows dropdown. --- 3. Replace
Errors / Replace Values Use Case: Replace " " (empty string) or null with "Not Provided" or 0.
Steps: Right-click column → Replace Values Replace null using Transform > Replace Errors ---
4. Trim and Clean Text Use Case: Remove extra spaces from Customer Name or Item
Description. Steps: Select column → Go to Transform > Format > Trim and Clean --- 5. Split
Column by Delimiter Use Case: Break down Shipping Address into Street, City, etc. Steps:
Select column → Split Column > By Delimiter (comma, space, etc.) --- 6. Extract Text (First
Name, Last Name, Domain) Use Case: Get domain from Email, extract city from Address.
Steps: Select column → Add Column > Extract > Text Before/After Delimiter --- 7. Change Data
Types Use Case: Ensure Order Date is in Date format, Units Ordered is Whole Number. Steps:
Click data type icon next to column name Or select column → Transform > Data Type --- 8.
Filter Rows Use Case: View only “Delayed” shipments or “High Discounts”. Steps: Click
dropdown on column → Apply filters manually Or use Home > Keep/Remove Rows >
Keep/Remove Top/Bottom Rows --- 9. Unpivot Columns Use Case: Turn Q1 Sales, Q2 Sales,
etc., into [Quarter], [Sales]. Steps: Select the Q1 to Q4 Sales columns Click Transform > Unpivot
Columns --- 10. Pivot Columns Use Case: Pivot Delivery Status to count status per region.
Steps: Select categorical column → Click Transform > Pivot Column Choose aggregation (e.g.,
Count, Sum) --- 11. Group By Use Case: Aggregate total sales per State or average Unit Price
by Item Category. Steps: Select column(s) → Transform > Group By Add aggregation logic
(Sum, Avg, Count, etc.) --- 12. Conditional Column Use Case: Flag high-value orders or late
shipments. Steps: Go to Add Column > Conditional Column Define logic like: If Total Cost >
5000 then "High Value" else "Normal" --- 13. Custom Column Use Case: Calculate delivery time
or adjusted discount. Steps: Go to Add Column > Custom Column Example:
[Link]([Shipped Date] - [Order Date]) --- 14. Detect & Replace Nulls Use Case: Show
“Missing Info” for null Phone Numbers. Steps: Right-click → Replace Values Replace null with
"Missing Info" --- 15. Remove Columns Use Case: Eliminate unnecessary fields like Email or
Description. Steps: Select column → Press Delete key or Home > Remove Columns --- 16.
Merge Queries Use Case: Combine data from another table (e.g., region info). Steps: Go to
Home > Merge Queries Select matching column, choose join type (Left, Inner, etc.) --- 17.
Append Queries Use Case: Stack multiple files (e.g., Jan, Feb, Mar data). Steps: Go to Home >
Append Queries Select tables to combine --- 18. Fill Down / Fill Up Use Case: Fill blank
Shipment IDs downwards. Steps: Select column → Transform > Fill > Down or Up --- 19.
Duplicate Queries Use Case: Test transformations without modifying original data. Steps: Right-
click query name → Duplicate --- 20. Sort Data Use Case: Sort by Order Date or Total Cost
Steps: Click dropdown in column header → Sort Ascending/Descending