Excel: Power Query – Transformations (Advanced)

Implementing Business Rules through Advanced Transformations

In this article, I’ll continue from the article [Power Query – Transformations] and illustrate some advanced transformations available in the Query Editor.

I’ll begin with the same data from the previous article.

Split Column By Delimiter

First, I wish to parse “Order ID” values so I end up with a new column containing just the order’s state.

First, I’ll select the “Order ID” column, then on the “Transform” tab, I’ll click the “Split Column,” then “By Delimiter.”

In the popup window, I’ll specify I want to split the column by the left-most hyphen, then press “OK.”

Now, I have two columns, the first my new column for states, the second, the remaining values.

Also, I have the last step in my history (“APPLIED STEPS”).

However, so I later remember what occurred at this step, I wish to rename it to something more intuitive.

Parse Month into New Column

Now, I wish to later analyze orders by month so I want a new column which contains only each order’s month.

First, I’ll select the “Order Date” column, then use the “Date” function’s Month tool.

Now, I have a new column at the end for each order’s Month.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s