Transforming Data with Power Query

Once the basic data quality issues have been addressed, you can reshape the dataset to support better analysis.

1. Renaming Columns

Clear column names help users understand the dataset.

For example, a source file might contain columns named:

• ProdNm
• Qty
• Amt
• OrdDt

You can rename them to:

• ProductName
• Quantity
• SalesAmount
• OrderDate

To rename a column, double-click its header or use the Rename option from the column menu.

Use meaningful and consistent names throughout the project.

2. Removing Unnecessary Columns

A source file may contain fields that are not needed for the report, such as temporary notes, unused descriptions, or irrelevant tracking fields.

To remove unnecessary columns:

1. Select the columns you do not need.
2. Open the Home tab.
3. Choose Remove Columns.
4. Review the remaining fields.

Removing unnecessary columns can simplify the data model and, in suitable cases, reduce memory usage.

Before removing a column, check whether it is required for a calculation, relationship, audit, or later transformation.

3. Filtering Rows

Filtering allows you to keep only the records relevant to the analysis.

For example, a sales report might need to exclude transactions marked as Cancelled.

To filter rows:

1. Select the filter dropdown in the relevant column.
2. Select the values to keep or define a condition.
3. Apply the filter.
4. Confirm that the resulting dataset contains the intended records.

You can also use numeric and date filters. For example, you might retain sales transactions from a particular year or exclude records with a zero quantity when required by the reporting rules.

Filtering should reflect the business requirements rather than simply removing unusual-looking records.

4. Splitting Columns

Sometimes a single column contains multiple pieces of information.

For example, a column named CustomerName might contain:

Arun Kumar

If the analysis requires separate first-name and last-name fields, you can split the column using a space delimiter.

To split a column:

1. Select the column.
2. Open the Transform tab.
3. Choose Split Column.
4. Select By Delimiter.
5. Choose the appropriate delimiter, such as a space or comma.
6. Review the resulting columns and rename them if necessary.

Be careful when splitting names because some people have multiple first names or family names. A simple delimiter-based split may not correctly interpret every record.

5. Merging Columns

Merging combines values from multiple columns into a single column.

For example, a dataset may contain separate City and State columns. You could combine them into a Location column.

To merge columns:

1. Select the relevant columns.
2. Open the Transform tab.
3. Choose Merge Columns.
4. Select a separator, such as a comma or space.
5. Provide a suitable new column name.
6. Review the result.

Merging is useful when the combined value is easier to use in a report or when preparing a descriptive label.

6. Creating Conditional Columns

A conditional column assigns values based on specified rules.

For example, a company may classify orders according to their sales amount:

Suppose the business defines these categories:

• High: SalesAmount greater than or equal to 50000.
• Medium: SalesAmount greater than or equal to 10000 but below 50000.
• Low: SalesAmount below 10000.

To create a conditional column:

1. Open Power Query Editor.
2. Select the Add Column tab.
3. Choose Conditional Column.
4. Enter the column name, such as SalesCategory.
5. Define the conditions in the correct order.
6. Set the output for each condition.
7. Apply the changes and inspect the results.

The thresholds are examples. In a real project, define categories according to the organization's requirements.

7. Understanding Append and Merge Queries

Power Query supports two important ways to combine data: append and merge.

Append Queries

Append combines rows from two or more tables into a single table.

For example, a company may maintain separate sales files for January, February, and March. If the tables have compatible column structures, they can be appended to create one sales dataset.

Merge Queries

Merge combines columns from related tables by matching values in one or more selected columns.

For example, a Sales table may contain ProductID, while a Products table contains ProductID and ProductName. Merging these tables on ProductID can bring the product name into the sales query.

The matching columns should contain compatible values and data types. Review the join type and the number of matching records before expanding the merged data.

These operations are useful when preparing data from multiple files or sources.