😎PowerBi - Data Cleaning

📘 Power BI Chapter 6: Power Query Editor

🔶 1. What is Power Query Editor?

Power Query Editor is a data transformation and preparation tool within Power BI Desktop. It helps users clean, reshape, and transform raw data before it's loaded into the Power BI data model.

🔶 2. Opening Power Query Editor

To open Power Query Editor:

• Click on "Transform Data" on the Home tab in Power BI Desktop.

🔶 3. Power Query Editor Interface

Section	Description
Ribbon	Contains commands like Remove Rows, Merge Queries, Group By, etc.
Queries Pane	Lists all queries (tables) loaded or created.
Data Preview	Shows a preview of your data with applied steps.
Applied Steps	Shows the list of transformations applied to the data.
Formula Bar	Displays the M code used for each step.

🔶 4. Basic Data Cleaning Operations

✅ Remove Columns

• Home → Remove Columns → Select and remove unnecessary columns.
• Shortcut: Right-click on the column header → "Remove".

✅ Remove Rows

• Remove Top Rows
• Remove Bottom Rows
• Remove Duplicates
• Remove Blank Rows

✅ Replace Values

• Replace nulls or incorrect values.
• Right-click → Replace Values → Specify "Value To Find" and "Replace With".

✅ Change Data Types

• Automatically detected or manually set (e.g., Whole Number, Decimal, Date, Text).

✅ Rename Columns

• Right-click on the column → Rename.

🔶 5. Column Transformations

🔹 Split Column

• By Delimiter (e.g., comma, space, etc.)
• By Number of Characters

🔹 Merge Columns

• Combine multiple columns into one.
• Option to add separators (e.g., space, comma).

🔹 Add Column from Examples

• Home → “Column from Examples”
• Provides a smart way to create columns by entering sample output.

🔹 Conditional Columns

• Add logic similar to IF statements (e.g., "If score > 50 then 'Pass' else 'Fail'").

🔶 6. Advanced Data Transformations

🔹 Group By

• Aggregates rows based on selected column(s).
• Options: Sum, Count, Average, Max, Min, etc.

🔹 Pivot & Unpivot Columns

• Pivot: Turns unique values in a column into multiple columns.
• Unpivot: Converts multiple columns into attribute-value pairs (useful for normalizing data).

🔹 Merge Queries

• Combines columns from two queries (similar to SQL JOIN).
• Types:
  • Left Outer
  • Right Outer
  • Inner
  • Full Outer
  • Anti Joins

🔹 Append Queries

• Adds rows from another table/query (like UNION in SQL).
• Used for combining data from multiple sheets or files.

🔶 7. Working with Dates

• Extract Year, Month, Day
• Extract Day Name, Month Name
• Create custom columns using Date.Year(), Date.Month(), etc.

🔶 8. Using M Language (Advanced)

M is the Power Query formula language. Every step in Power Query generates a line of M code. Example:

= Table.SelectRows(Source, each [Age] > 30)

🔶 9. Applied Steps

• Each transformation you perform is recorded.
• You can:
  • Rename steps
  • Reorder steps (carefully)
  • Delete or modify any step

🔶 10. Loading Data

After applying all transformations:

• Click Close & Apply to load data into Power BI.
• Data is now clean and ready for modeling and visualization.

🔶 11. Best Practices in Power Query

• Remove unnecessary columns early to reduce memory.
• Use descriptive names for steps and columns.
• Avoid hardcoding values (use parameters instead).
• Minimize the number of steps for performance.
• Use staging queries to simplify complex transformations.