Merge Columns in Power Query for #Excel

Merging columns is a super easy task in Power Query. What we need to know is where to click.

Having said that, we need to pay attention to

  1. The order of selecting columns to be merged, as it directly impacts on the result;
  2. The difference between doing it under Transform tab or Add Column tab in the Power Query Editor.

You may download a sample file to follow along:

Order of selecting column

This is indeed intuitive. When we try to merge multiple columns in Power Query, the first step we do is to select the columns to be merged. Power Query will remember the order of our selection.

For example, if we want the result to be “First and Last Name”, then we should select the column “First Name” first, “Last Name” last. On the contrary, if we want “Last and First Name” as a result, we should then select the column “Last Name” first, followed by “First Name”.

Very straightforward!

Transform or Add Column

On the ribbon of Power Query Editor, we see a few command buttons under both Transform and Add Column tabs. Merge Columns is one of those buttons.

Why same action under different tabs?

It seems confusing but it is not. The difference is how we want to see the output.

  1. If we don’t want to keep the original columns, use the command button under Transform tab;
  2. If we want to keep the original columns, use the command button under Add Column tab

As simple as this! 😉

Watch it in action!

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.