Are you struggling with merging columns in Power Query? Don’t worry! In this comprehensive guide, we’ll walk you through the process step by step so you can easily merge columns in Power Query.
Step 1: Convert Data into a Table
To begin, you need to convert your data into a table. There are two simple methods to do this:
Go to the “Insert” tab, select the range containing your data, and convert it into a table.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.3_Merge-Columns-using-Power-Query.png)
Alternatively, navigate to the “Data” tab, choose “From Table/Range,” and Power Query will automatically convert your data into a table.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.5_Merge-Columns-using-Power-Query-1024x664.png)
Step 2: Access Power Query
Once your data is in table format, Power Query will open automatically. Here, you’ll see all your columns displayed neatly.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.7_Merge-Columns-using-Power-Query-1024x477.png)
Step 3: Merge Columns
Now, let’s merge the columns. Suppose you want to merge the “First Name” and “Last Name” columns to create a “Full Name” column. Select both the “First Name” and “Last Name” columns (use shift key to select multiple columns).
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.9_Merge-Columns-using-Power-Query-1024x482.png)
Click on the “Merge Columns” option under Transform menu.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.11_Merge-Columns-using-Power-Query-1024x436.png)
Step 4: Configure Merge Settings
A popup window will appear with merge options.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.13_Merge-Columns-using-Power-Query-1024x376.png)
You can choose a separator to distinguish between merged values. Typically, a space works well for creating full names. Select the space character as the separator.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.15_Merge-Columns-using-Power-Query-1024x469.png)
Name the new column “Full Name” in the designated field.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.17_Merge-Columns-using-Power-Query-1024x366.png)
Click “OK” to proceed.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.19_Merge-Columns-using-Power-Query-1024x366.png)
Step 5: Review and Load Data
Once merged, the “Full Name” column will appear, combining the first and last names seamlessly.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.21_Merge-Columns-using-Power-Query-1024x345.png)
Click on “Home” and choose “Close and Load.”
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.23_Merge-Columns-using-Power-Query-1024x541.png)
The merged data will be loaded into a new sheet, ready for further analysis or presentation.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.24_Merge-Columns-using-Power-Query.png)
Alternate Method to keep Original Columns
Step 1: Delete the recently added merged columns step.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.25_Merge-Columns-using-Power-Query-1024x348.png)
Step 2: Under “Add Column” menu, click on “Custom Column”
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.27_Merge-Columns-using-Power-Query-1024x409.png)
Step 3: Write the new column name as “Full Name” and mention the formula as “=[First Name]&” “&[Last Name]”.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.29_Merge-Columns-using-Power-Query-1024x621.png)
Step 4: Make sure there is no syntax error in your formula. Once done, click on Ok
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.31_Merge-Columns-using-Power-Query-1024x621.png)
Step 5: You will notice this time a new column “Full Name” is added at the end.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.33_Merge-Columns-using-Power-Query-1024x324.png)
Step 6: Once done with all query changes, click on “Home” and choose “Close and Load.”
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.35_Merge-Columns-using-Power-Query-1024x549.png)
Step 7: Your newly merged data will be loaded into a new sheet, ready for further analysis or presentation.
![Merge Columns using Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/6.37_Merge-Columns-using-Power-Query-1024x424.png)
That’s it! You’ve successfully merged columns using Power Query. Whether you prefer the built-in merge option or custom formulas, Power Query offers versatile solutions to meet your data manipulation needs.
Thank you, we hope this guide has been helpful in enhancing your data management skills with Power Query. Stay tuned for more insightful tutorials!