Extract or Combine First and Last Names in Power Query

Working with first and last names in Power Query is a fundamental skill. Whether you’re parsing them from a single column, or combining multiple columns into one for easier readability, Power Query gives users multiple methods to complete data transformation quickly and efficiently.

Working with First and Last names in Power Query can be a big time saver. Learn how to Merge multiple columns into one, convert a middle name to an initial or separate one column into many

Working with first and last names in Power Query is a great way to learn how to parse or combine columns. Luckily, Power Query lets you do both without needing to know any formulas. For advanced scenarios it also supports several Power Query M formulas that give developers more control over the process. We’ll explain both.

Let’s jump in!

Why You Should Use Power Query

Power Query is available across a multitude of different Microsoft products. The biggest ones are Excel and Power BI. When combining first and last names there are multiple benefits to using Power Query even though it can often be achieved with Excel formulas or Power BI DAX.

When you build data transformations in the Power Query Editor, the steps are recorded so that they can be easily applied to future datasets. Meaning that you can set up a data transformation once and re-use it many times. In the case of Power BI, the transformations can be scheduled to refresh at a specific time of day compared to DAX calculations that occur on user interaction. And in the case of Excel, transformations occur when you refresh a table instead of having to wait for a formula to calculate.

In both platforms Power Query is a big time saver.

Power Query vs. Excel – Working with Columns

Excel is a great tool for data analysis, but when it comes to handling large datasets or performing complex transformations, it can become cumbersome. Power Query, offers a more efficient approach to managing data transformations.

Instead of users creating formulas in Excel and copying and pasting them, columns. formulas in Power Query are created at the column level. This means that all of your data has to be in a continuous table to be able to work with it.

In our examples we’ll look at combining First and Last Names, which will be applied to a full column of data. Power Query does not allow users to easily apply a formula to a single cell at one time like you would in regular Excel spreadsheets.

Combining First and Last Names in Power Query

Combining first and last names is a common task in data preparation. In Power Query, this can be effortlessly achieved by using the “Merge Columns” feature. Select the columns you wish to combine, choose a separator, and specify a new column name.

This process transforms two separate name fields into a single, formatted name field.

To get started, select the columns you want to merge together.

You can select multiple columns by holding down CTRL + Left-Click to select multiple columns. You can also hold down SHIFT + Left-Click to select a range of columns.

Once the columns are selected, press Merge Columns from the Transform tab of the Power Query Ribbon.

Example of selecting a first and last name column in Power Query to Merge Columns

A dialog box will appear that allows you too choose the Separator. This is the character that will be between the two column values.

In our case, we’ve selected Space and provided a new column name “First Last Name”

The Merge columns dialog box prompting for a separator and a new column name

Once complete the two selected columns are merged into a single column, and the original two columns are removed from the dataset.

Example of a newly created first and last name column in Power Query

How to Combine First, Middle, and Last Names

Power Query is not limited to only merging two columns at once. You can select multiple columns by either holding CTRL + Left-Click to multi-select different columns or SHIFT + Left-Click to select a range of columns. The select “Merge Columns” from the Transformation tab of the Power Query Ribbon.

Combining more than two columns into a single column with Power Query Merge Columns function

When a dialog box appears, select a separator, in this case we’ll choose a space.

The merge column dialog box for multiple columns to create a full name column

After pressing OK, all 3 columns will be removed and replaced with a single column that contains First, Middle, and Last Name.

Combining First and Last Names with Power Query M

For more control over the process of combining First and Last names in Power Query, and to avoid the elimination of the original source columns, you can add a new custom column with a Power Query M formula. The syntax is: [First Name]&” “&[Last Name].

The name of each column is represented in square brackets, the ampersand & symbol tells Power Query to concatenate, and a space in-between double quotes tells Power Query to assess a space as text.

To start, select Custom Column from the Add Column tab of the Power Query Ribbon.

A dialog box appears where you can type in formulas. The example below shows concatenating two columns together with a space in-between. We added extra spaces in-between each element to make it easier to read, the spaces are optional when writing Power Query M formulas.

Using Power Query to concatenate columns

After Pressing OK a new column is created to the right of the other data in your data set. This effectively merges the values in the columns without losing the original columns.

Converting Middle Names to Abbreviations

To convert a middle name to a middle initial in Power Query you can combine a couple of useful formulas when creating a custom column. We’ll explain how to extract the first letter, and concatenate a period at the end of it using the Power Query M syntax: Text.Combine({Text.Start([Middle], 1), "."}),

Let’s break it down into two separate components.

Text.Start

Text.Start([Middle], 1) extracts the first letter of the middle name from the [Middle] column. The 1 indicates that only the first character is taken, serving as the initial.

Text.Combine

Text.Combine({Text.Start([Middle], 1), "."}) then combines this initial with a period. The {} creates a list of the initial and the period, which are joined without any space, producing a formatted initial like J..

You could also concatenate a period with an & symbol after surrounding it in quotes, but the Text.Combine function is fairly common and we wanted to at least introduce it for future familiarity.

Parsing Names with the Split Column and Extract Functions

Power Query gives users two different ways to split a column into multiple column. The first, is the Split Column function which works similar to a traditional de-limit. You tell Power Query that you want to create a new column every time there is a space and it will convert the text between spaces into new columns. The second option is the Extract button, this gives you more granular control where you could say to parse before a specific character and after a specific character.

The example below highlights both the split column and extract features.

De-limiting and extracting text or splitting columns in Power Query

Your individual data will drive which one you end up using. We really appreciate the Extract feature when working with complex datasets as it almost gives you as much control as Regular Expressions (RegEx) which is not supported in Power Query.

Parsing Names with Columns from Example

Power Query’s “Column from Examples” feature uses artificial intelligence to understand data manipulation tasks from user-provided examples. To use it, press the “Column From Examples” button under the “Add Columns” section of the Power Query Ribbon. Tell it to either consider selected columns or all columns.

The right side of the interface lets you type in the desired output and it will auto-fill down with grey text when it detects the pattern.

The example below shows the First Middle and Last Name being merged together based on the first cell input being the desired outcome.

The column from example editor in Power Query used to combine and merge columns

When you press OK, a new custom column will be generated without having to write any Power Query M code. This method works well for simple tasks and may require additional output examples for more complex ones.

Conclusion

Whether you are merging or separating first, middle and last names in Power Query there is typically a no-code method to achieve it or you can apply Power Query M formulas for more granular control. These techniques are equally valuable when working with text or csv files that need to be parsed or other columns that have to be combined or separated.

Scroll to Top