Purpose: to combine columns (concatenate) in Power Query with text and numeric data types
Links to topics on this page
- Open a data table in Power Query
- Concatenate text columns in Power Query
- Syntax: combine text
- Concatenate text and numeric columns in Power Query
- Syntax: combine text and numeric data
- How to enter formulas in Power Query
- Related topics
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 >>.
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.
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 &”_”&.
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.
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
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.