INTRODUCTION Data cleaning is the process of identifying and correcting errors, anomalies and inconsistencies in raw data sets to improve the quality of the data and get it ready for advanced analysis and modeling. In today’s data-driven world, raw data is often messy and rarely ready for analysis. The real value of a data analyst lies not just in collecting data, but in their ability to prepare it for meaningful insights. Data cleaning and transformation is essential because it organize tables, remove duplicates, simplify complicated columns through merging or appending, remove duplicates and null values, improve result accuracy for better and informed decision making. This is where ETL (Extract, Transform, Load) becomes essential. Power Query Editor in Power BI Desktop allows you to shape (transform) your imported data. Power Query Editor only modifies a view of your data, so you can be confident that your original data source remains unchanged. Power BI’s data preparation workflow through the Power Query Editor is where: Each step performed is recorded, making your workflow reproducible and easy to modify. Working with Power Query Editor 1. Promoting Headers Steps: Go to Home Column Use “Use First Row as Headers” Ensure columns are properly labeled for easier analysis 2. Creating Index Columns Steps: Go to Add Column → Index Column Choose: From 0, From 1 or Custom increment (which define a starting value and increment, e.g., 3, 6, 9…) 3. Conditional Columns and Logic Steps: Navigate to Add Column → Conditional Column Define logic like: If Salary = 50,000 → “Full Employee” Else → “Contract Worker” 4. Column from Examples Steps: Go to Add Column → Column from Examples Provide sample outputs Power BI automatically detects the transformation logic. 5. Unpivot and Pivot Columns Steps: Go to Home → Transform Select column to unpivot/pivot Click Pivot column to convert Chose value column (e.g., Sales) to populate the pivoted data Click Ok To Unpivot, click unpivot and unpivot columns Press Ok 6. Grouping and Aggregation Steps: Go to Transform → Group By Apply aggregations like: Sum, Count, Average, Min/Max Example: Total sales by region Average revenue per customer 7. Merge and Append Queries Merge Queries (Join) extract a particular column from one table to join another table through the use of a common key. E.g. merging customer and transaction tables. Append Queries (Union) concatenate rows from two tables with the same structure into a single table. E.g. appending monthly datasets. Step: To Merge Open Power query editor through the transform data Select the table to merge from the left pane (e.g., CodeSphere Hub Sales 2019) Click on the Home Tab and select the Merge Queries Choose “Merge Queries” to merge directly into selected table or “Merge Queries as New” to create a new merged table Select the data table from the first drop down and select the common column (e.g., Product_Key) Select the second table from the second drop down and chose the matching column (I.e. Product_Key) Click ok Step: To Append Open Power query editor through the transform data Select the table to append (e.g., CodeSphere Hub Sales 2019) Click on the Home Tab and select the Append Queries Choose “Append Queries” to append directly into selected table or “Append Queries as New” to create a new appended table Select the first query from the first drop down selection (e.g., CodeSphere Hub Sales 2019) Select the second query from the second drop down selection (e.g., CodeSphere Hub Sales 2020) Click ok 8. Date and Time Transformations Step: Open Power query editor Navigate to Transform → Date or Navigate to Transform → Time to extract values (Day, week, month, quarter, year). To standardize date format, Navigate to Data Type, Select Date/Time, Date or Time to ensure accurate modeling. Using DAX functions like DATEDIF to calculate differences 9. Adding Prefix and Suffix Using Power Query Steps: Click on Transform data to open Power Query Editor Select the column to edit Navigate to the Transform Tab Click Format Add Prefix or Suffix Enter the desired text/character in the dialog box (e.g. add “NGN” before currency values) Also enter desired text/character for suffix and Click OK Or use Custom Column with formulas 10. Data Profiling Techniques Column Profile: which shows the entire statistics of a selected column and it shows statistics like Count, Error, Empty, Distinct Unique, Not Available Number (NAN), Min, Max, Zero, Avg, Standard Deviation, Even and Odd. Column Distribution: shows a small histogram under the column header that visualize the frequency and distribution of values. It explicitly list out the number of distinct values and Unique values in a selected column Column Quality: displays valid data, empty values and percentage of errors Steps: Click on Transform data to open Power Query Editor Select the column to profile Navigate to the View Tab Enable the option to preview by checking the box and unchecking others (e.g. check the box to preview column quality) Column Profile Column Distribution Column Quality 11. Handling Missing or Null Values Steps: First, identify missing data through the column quality to see the percentage of valid, error and empty (null) values in every column Click the filter arrow on the column header Select null (or blank) to view only rows with missing data To replace manually, Right click a column Select Replace Values Type Blank in the “Value to Find” Enter replacement (e.g., 0 or Unknown) in the “Replace With” To replace null and empty value by filling down, Select the column Go to Transform Tab Click Fill and select Down It copies the last non-null value into the subsequent null cells until it hits a new value It is also applicable if the “summary” or “header” values us located below the missing data rows. To remove Missing Data Select column Click on filter arrow and chose remove Empty. CONCLUSION Data preparation is the most critical step in any analytics workflow. Power BI’s Power Query Editor provides a robust and flexible environment to clean, transform, and load data efficiently. By mastering these techniques from basic cleaning to advanced transformations you position yourself to build accurate models, insightful dashboards, and impactful data stories. The difference between average and exceptional analysis often lies not in visualization but in how well the data was prepared.