Purpose: to combine columns (concatenate) in Power Query with text and numeric data types

Links to topics on this page
Columns

How can I merge two columns of data into one like the following. Excel Formula to compare two columns and if match found check the next cell for a value and then. Data, Get Data, Combine Queries, Merge. Merge two queries with differing columns; From the top drop down in the Merge dialog, choose the first query. From the second drop down in the Merge dialog, choose the second query. Click on the Product heading in the top preview (this is the key field.

Two
In this post we show how to combine text columns and strings together in Excel’s Power Query, and then how to combine text columns with numeric data columns.

Open a table in Power Query

To start using Power Query in Excel you need to load data — this can be from a table within your Excel workbook, or from external sources such as other workbooks, online sources, or even text/csv files.

TIP: To make it easy to import an existing cell range into Power Query, first convert the range of cells into an Excel Table. To find out more about the benefits of using Excel Tables see our post here >>.

Columns

In your Excel worksheet, click anywhere in your Excel Table, go to the Data tab on Excel’s ribbon, and select ‘From Table/Range‘.

The Power Query Editor window opens.

Concatenate in Power Query – combine text columns

Combining data in columns can be useful, e.g. to create a helper column for lookups.

To combine columns in Power Query, we use the ‘Custom column‘ option. In the Power Query Editor window, go to the ‘Add Column‘ tab, and click on the Custom Column icon.

TIP: By using Custom Column we’re not overwriting our existing columns.

In the Custom Column dialog box, you name the new column, and enter your formula in the custom column formula box. You can also name or rename the column header in the main Power Query Editor window.

Combine Two Columns In Excel

For more help with entering formulas in Power Query, see below >>.

TIP: Excel helpfully tells you, if you click on the i information symbol above the box, that you can press Ctrl-Space to display the list of available columns and other formula suggestions.

Syntax – concatenate text data in Power Query

=[Column1] & [Column2] & “_” & [Column3]

The basic syntax to concatenate in Power Query, is to add column names in square brackets [ ], separated by the & (ampersand) symbol. To include additional text strings, enclose the strings with double quote marks, e.g. to separate the column data with an underscore, enter &”_”&.

Combine two columns in excel with dash

Concatenate in Power Query – combine text and numeric data columns

The method above won’t work with combining text and numeric data columns in Power Query.

If we use the same syntax as above, an Error will appear in the column rows. Click on ‘Error’ in one of the cells, and the full error message is displayed — ‘We cannot apply operator & to types Text and Number’.

Instead we need to add the ‘Number.ToText()’ function to convert the number to text format. Note that we need to wrap our column name within round brackets () as this is a function.

Combine
Syntax: combine text and numeric data columns in Power Query

= [text_column] & Number.ToText([number_column])

Return to the step in the Query Settings ‘APPLIED STEPS‘ pane in the Power Query Editor window, and click on the wheel settings icon to edit the step.

Now we enter the correct formula and we have combined our text and data column.

Power Query: how to enter formulas

When entering formulas in Power Query, e.g. in the Custom Column dialog box, you’ll find the box to enter your formula, with a list of available data columns to the right.

Adding column names to your Power Query formula
  • Type the column names in manually, enclosing them in square brackets [Column_name];
  • Double-click on the column name for it to be added to the formula box;
  • Select the column from the right-hand pane, and click on the ‘Insert’ button.
Entering commands and functions in Power Query formulas

Similar to in Excel generally, when you start typing a command or function in the Power Query formula box, formula suggestions will appear. Click on a command to select it.

Hover over the command, and a tooltip will appear, describing the syntax and arguments for the command.

Errors in Power Query formulas

Combine Two Columns In Excel 2010

Excel will alert you to a syntax error at the base of the window — however, even if there is a green tick, this doesn’t necessarily mean that your formula will work … there may be other errors that you won’t find until you click OK and run the formula.

Related topics

For many Microsoft Excel users, combining two columns into one can be an especially helpful function. If you have a list of names with one cell containing a first name and one cell containing a last name, you might want to merge them into one cell, where the first and last name appear together.

This tutorial will teach you how to combine two columns into one in Microsoft Excel.

Let’s say you have three names. The last names are in column A and the first names are in column B. It looks like this:

If you want to combine the first name and last name together with the first name proceeding, follow these steps.

Let’s put these combined names in column C.

For ‘Joe Smith’, we’ll start entering our combination formula in cell C1.

In cell C1, we type: =B1&” “&A1

It looks like this:

The product of this formula renders the name in C1 like this:

Combine Two Columns In Excel Into One Column

In the formula, putting B1 first references Joe. Adding &” at the end lets Excel know you want to perform a combination. Using A1 second references Smith. Preceding it by “& let’s Excel know you want to combine it.

Combine Two Columns In Excel With Space

We can do the same procedure for the other two names. For example, if we wanted to combine Bob Regan’s name into cell C2, we would type into C2: =B2&” “&A2

However, re-entering the formula for each name is tedious and unnecessary. Rather, we can click the bottom right corner of cell C1, which contains Joe Smith, and drag the corner down to have Excel replicate the formula. After performing this task, our spreadsheet looks like this:

You have now learned how to combine two columns into one in Microsoft Excel.

Related Tutorials

Coments are closed

Most Viewed Posts

  • Abraxas Sigil
  • Prime Video Account Free
  • Tableau Reader Mac
  • Tableau Prep To Tableau Desktop
  • Onion Deep Web Download

Scroll to top