How to use Power Query Concatenate to join data in Excel
By
SpreadCheaters
Let’s consider the following data set which contains the details about the soccer team players.
We’ll use the custom query on this data to combine data from two columns to explain how Power Query can be used to combine the data columns i.e. First Name and Last Names and create a new column Full Names. So let’s dive into it and learn how to do it.
Power Query is considered to be the most powerful data preparation and cleansing tool found in Excel 2010 and later. Power Query allows a user to import data into Excel through external sources, such as Text files, CSV files, Web, or from within the same Excel workbook. The data can then be cleaned, converted, and restructured using Power Query. The most important feature of Power Query is that you can put up a query once and then re-use it by simply refreshing. In this tutorial we are going to learn how to use Power Query to concatenate data columns consisting of different types of data.
Step 1 – Create a data table for Power Query
– As the first and foremost step, we’ll convert our ordinary data into an Excel Table.
– To do this simply select any cell inside the data set and press CTRL+T. A new dialog box will appear asking you to select the data range and check if your Table has Headers.
– If you wish to include more cells in the table then you can change the selection here, otherwise you may leave the automatic selection done by Excel as it is. Our data had headers so we’ll keep that checked.
Step 2 – Open Power Query Editor
– Go to the Data tab in the top most lists of tabs and then locate From Table / Range in Get & Transform Data.
– In there click on From From Table / Range button.
Step 3 – Open custom column dialog in Power Query Editor
– The last step will open up the Power Query Editor and now we will add a custom column where we can concatenate the First and Last Names.
– For this go to Add Column and then press the Custom Column button.
Step 4 – Create the query to concatenate using Power Query
– The last step will open up a new dialog box with the name “Custom Column”. Now it is the time to write the query to concatenate the First and Last Names.
– Write a meaningful name in the “New Column Name” field. We have written “Full Names”.
– Then double click on the field’s Names one by one which you would like to concatenate as shown above.
– The “&” sign is used to concatenate the data from two columns and “ ” is used to add a space between the data from both columns.
Step 5 – Concatenate the data using Power Query
– The last step is simply to press the enter button on “Custom Column” dialog after writing the query as shown in the last step.
– This will merge data as desired.
