Power Query: Concatenate Values (Text and Numbers)

In Power Query, there are multiple methods that you can use to concatenate values (numbers and text). And in this tutorial, we will learn to use these methods.

Below we have a list of first and last names in the columns:

Concatenate Values from Two Columns into One (Combine Columns)

You can use these steps:

1. First, load your data into the power query editor (Data > Get Data > From Other Sources > From Table/Range.
   
2. Afterward, select both columns in the editor and go to Transform > Merge Columns.
   
3. When you click on the “Merge Columns”, you will get a dialog box that selects the separator and the name of the new column.
   
4. Once you select the separator and column name, click OK. And hit enter to get the new column.
   

Now, we have a new single column with values from both columns (first and last name). While you use this method, you need to take care of a few things:

1. Select the columns in the sequence you want to combine the values.
2. You can enter a custom separator in the dialog box.

 Concatenate Columns by Adding a New Column

Unlike the above method that we have used, you can also use a custom column and function to combine values.

1. Go to the Add Column > Custom Column.
   
2. In the dialog box, you need to write a formula.
   
3. Enter the column’s name and then enter the formula we used in the below snapshot.
   
4. Finally, click OK to enter the formula and create a new combined column.
   

In this formula, we have used an ampersand to combine values from both columns with a space in between. It is like a formula that we use in Excel.

=[First]&" "&[Last]

Combining Text and Number into One Column

In Power Query, if you try to combine the text with numbers, you will get an error in the final value.

In this situation, you need NumberToText function to convert the number into text.

And once you click OK after entering the formula. You will get a new custom column.

Combining Values from a Single Column

If you have values in a column and want to get them in a single cell, you need to transpose them first.

Select the column and click on “Transpose”.

Select the separator from the merge columns dialog box, enter the column name, and click OK.

You will get a new column with the values combined when you click OK.