How to Concatenate Two Columns in Power Query

Data manipulation and transformation are crucial skills for any data analyst. One of the common tasks is concatenating columns to combine them into a single column, making it easier to handle the data. In this guide, we will walk you through the process of concatenating two columns in Power Query.

Introduction to Power Query

Power Query, available in Microsoft Excel and part of the Power BI suite, is a powerful data transformation tool that allows you to access, combine, and clean data from various sources for analysis. It provides a user-friendly interface to manipulate data without the need for complex syntax.

Steps to Concatenate Two Columns in Power Query

Open Power Query Editor: First, you need to load your data into Power Query. If you are working in Excel, select the data range, go to the Data tab, and click on Get Data or Power Query. If you are using Power BI, import your data directly into the Power BI service. Select the Columns: Once your data is loaded, you will see a Query Editor window on the right side. Select the two columns you want to concatenate. You can select multiple columns using the Shift key or the Ctrl key. Add a Custom Column: Go to the Add Column tab, and then click on Custom Column. This will allow you to write a formula for the new column based on your desired concatenation. Write the Concatenation Formula: In the Custom Column dialog, you can use a formula to combine the selected columns. Assuming your columns are named Column1 and Column2, the formula to concatenate them would look like this:

[Column1]     [Column2]

If you want to add a delimiter such as a space or a comma, you can modify the formula accordingly. Here is an example for a space-delimited concatenation:

[Column1]     [Column2]

If you want to add a comma and a space as a delimiter:

[Column1]  ,   [Column2]

Name the New Column: Give your new column a clear and descriptive name. Click OK to apply the new column to the data. Close Load: Once you have completed your transformations, click on Close Load to load the data back into your worksheet or data model. This will save your changes and return the modified dataset to your Excel or Power BI screen.

Example

Let's consider an example where you have two columns named FirstName and LastName. If you want to create a FullName column, your formula would look like this:

[FirstName]     [LastName]

When you apply this formula, Power Query will generate a new column with full names such as 'John Doe'. This makes it easier to reference the data in your reports or further analysis.

Advanced Use Cases

Power Query also supports the creation of custom data types. For instance, if you need to concatenate multiple columns, you can create a custom function using the Custom Column tool. You can then write a function to handle the concatenation, such as:

[ProductCode]   -  [UnitPrice]

This will create a new column for each record with a format like ProductCode - UnitPrice.

Best Practices

Ensure that the columns you are concatenating do not contain unwanted characters or null values that might affect the final output. Always test your formulas on a subset of the data before applying them to the entire dataset to avoid unexpected results. Paginate your data when working with large datasets to prevent performance issues.

By mastering the art of concatenating columns in Power Query, you can streamline your data processing and make your analysis tasks more efficient. If you have any questions or need further assistance, feel free to reach out.