Day 5: Power Query Editor – Transforming Data & Managing Columns in Power BI

On Day 5, we focus on transforming data and understanding how to manage and manipulate columns efficiently in Power Query Editor. These operations help ensure your dataset is structured, clean, and ready for meaningful analysis.

1. Transform vs Add Column

Understanding the difference between these two is crucial for effective data handling:

• Transform – Changes are applied directly to the existing column. For example, if you convert a text column to uppercase using a transform operation, the original column itself is updated.

• Add Column – Creates a new column without affecting the original data. This is useful when you want to perform calculations or transformations but still keep the original values for reference.

Add Column options include:

• Columns from Examples – Allows you to create new columns by providing sample values, and Power BI automatically deduces the logic.

• Custom Column – Write your own formulas using Power Query’s M language.

• Conditional Column – Creates a column based on a condition, similar to an IF statement in Excel.

2. Index and Duplicate Columns

• Index Column – Adds a sequential number to your dataset. Useful for tracking or sorting:

  • From 0 – Index starts from 0

  • From 1 – Index starts from 1

  • Custom – Start from a number of your choice

• Duplicate Column – Creates a copy of an existing column. This is handy if you want to test transformations or perform calculations without altering the original column.

3. Home Tab Functions

The Home tab in Power Query contains essential options for managing data sources, applying changes, and refreshing previews:

Close and Apply:

• Close & Apply – Saves your transformations and loads the data into Power BI for reporting.

• Close – Exits Power Query without applying changes.

• Apply – Applies transformations but keeps Power Query open for further edits.

New Data Source & Recent Data Sources:

• Easily add new datasets or connect to previously used sources without starting from scratch.

Enter Data:

• Manually input small datasets directly into Power BI.

Refresh Preview:

• Refresh Preview – Updates the data view in Power Query to reflect recent changes.

• Refresh All – Reloads all queries from their sources.

• Cancel Refresh – Stops the refresh process if needed.

Properties:

• Modify query names, descriptions, and load settings to keep your workspace organized.

Power Query Editor on Day 5 teaches us how to carefully transform data without losing original information, track and index records, and manage data sources efficiently. Mastering these functions ensures your datasets remain clean, consistent, and ready for analytics.