Excel: Power Query – Transformations

Implementing Business Rules through Transformations

In this article, I’ll continue from the article Excel Power Query – Introduction and illustrate some transformations available in the Query Editor.

I’ll begin with the data loaded into the Query Editor.

Since I don’t need the field Ship Mode, I’ll use “Remove Columns” to remove this column.

Now, I’ll use “Split Column” to separate the Customer’s first and last names.

Now, I’ll use the “Group By” feature to collapse orders by CustomerID to see the order count by customer.

I’d like to undo the last group by step, so I’ll use the Query Settings to remove that step.

Now, I’ll delete the last step.

Should I need to change any of the field’s data type, I can click the column and “Data Type” to select a new data type.

Also, I can decide to keep or remove certain rows of data.

For more transformation options, select the “Transform” tab.