Power Query is a data preparation and transformation engine with a GUI. It can fetch data from various sources and has a powerful editing interface that enables the Extraction, Transformation, and Loading (ETL) processing of data.

Since the engine is available in many services and products, the data storage destination usually depends on where Power Query was used. Through this article, we will learn how to use Power Query to merge columns in Excel. 

Source 

Merging Columns

Power Query allows us to merge two or more columns of Text Data Type in our query. We can combine columns to replace them with one column or create a new one adjacent to our merged columns. It's ideal to make a new column and keep the original columns to avoid any possible errors.

Users commonly ask, "How do I combine columns in Power Query?." This will be covered further in this article. 

Source

Merge Columns to Replace Existing Columns

We will use the MS Excel Power Query Merge Columns feature in this example. After merging columns, the selected columns turn into a single column called Merged. You may consider duplicating the original columns if the unmerged data may be still required. The original columns will no longer be available. In the table below, for instance, we will merge OrderID and CustomerID.

1. Locate a previously loaded query from Power Query Editor, select a cell in the data, and select Query > Edit. This link teaches how to create, load, or edit a query in Excel. 
2. Columns must be Text data type. If not, then select the column, and then click Transform > Data Type > Text.
3. Select the columns by pressing Shift+Click or CTRL+Click on each subsequent column that needs to merge. The order of selection will set the order of the merged values.
4. Click Transform > Merge Columns.
5. Specify a separator to insert between each merged column in the Merge Columns dialog box. We can also select from available predefined separator values or specify custom separator values.

Source

1. Click OK. Click to learn how to Rename a column. The result is as shown below:

Merge Columns to Create New Columns 

In this example, we will insert a custom column into the query table and use a custom column to concatenate text columns. The merged columns will still be available along with the new merged column in the query table.

Let us see how we can merge OrderID and CustomerID, separated by a space character:

1. Locate a previously loaded query from Power Query Editor, select a cell in the data, and select Query > Edit. Learn how to create, load, or edit a query in Excel on this link. 
2. Columns must be Text data type. If not, select the column and click Transform > Data Type > Text.
3. Click Add Column > Custom Column. The Custom Column dialog box will appear.
4. Select the first column in the Available Columns list and click Insert. We can also double-click the first column. The column will be added to the Custom Column Formula box immediately after the equal sign (=). Press Ctrl + Space in the Custom Column Formula box to display columns and other elements of the formula.
5. Enter an ampersand (&) in the Custom Column Formula box after the first inserted column. The ampersand (&) is the AND operator which combines values in Power Query in the same way as in Excel. Additional separators can also be specified, for example, using a space character by entering " " after the ampersand (&).
6. Enter another ampersand (&) after the space character in the Custom Column Formula box.
7. Select the second column in the Available Columns list and click Insert. We can also double-click the second column.

Source 

1. Press OK. The result is as shown below:

Source

Transform Your Data with Power Query

Power Query (also known as Get & Transform data in Excel) allows us to import or connect to external data and shape it. For example, one can change a data type, remove a column, or merge data tables as per requirement. We can also load our query into Excel to create charts and reports and refresh the data periodically to keep it updated. Power Query is available in Excel for Windows, Mac, and online. 

Power Query is also part of Power BI and is used to perform the ETL Transformation and load data. Power Query is also called Query Editor. We can use the Power Query Editor to connect data sources, modify as per requirements, and shape and transform data. You can later load that model into Power BI Desktop (or web service). It provides the same Power Query merge rows based on column and Power Query merge Excel tables with different column features. 

If you want to delve deeper into Excel and its other equally useful features, check out our Microsoft Excel Courses, starting from Beginner to Expert, to learn more.