Merge Columns in Power Query

February 27, 2023 - by Bill Jelen

Problem: I have first name in column A and last name in column B. I would like to concatenate those names and convert to proper case.

Strategy: Use Merge Columns in Power Query.

Select all of the columns that you want to merge. From the Add Column tab in the Power Query ribbon, choose Merge Columns.

You have first name in column A and last name in column B. Select both columns in Power Query and choose Add Column, Merge Columns. — Figure 1034. Select the columns to be merged and then choose Merge Columns.

The Merge dialog offers various separators to use between the colums: None, Colon, Comma, Equals Sign, Semi-colon, Space, Tab, or Custom.

Choose Space from the Delimiter drop-down menu. The default name for the new column is Merged. Type a meaningful name such as Name.

In the Merge Columns dialog, specify the Separator is a Space and the New Column Name is Name. — Figure 1035. Specify a separator between each column.

To convert the results to proper case, go to the Transform tab in the Power Query ribbon. Open the Format drop-down menu and choose Capitalize Each Word.

With the names merged, select the new column and go to the Format drop-down menu. Choose Capitalize Each Word. Other choices here are lowercase, UPPERCASE, Trim, Clean, Add Prefix and Add Suffix. — Figure 1036. Convert text to proper case.

This article is an excerpt from Power Excel With MrExcel

Title photo by Nil Castellví on Unsplash