A User-Friendly Guide to Merging Sheets in Excel Using Power Query Interface

In my previous articles, I shared a story about helping a doctor in Canada who needed to merge two sheets in an Excel workbook for data analysis. I showcased the technical solution using Power Query M code. For those who prefer a more user-friendly approach, here's how you can achieve the same result using the Power Query interface.

The Challenge Recap

The doctor had two separate sheets of data that needed to be combined for comprehensive analysis. Manually merging these sheets was not practical, and she required an automated, reusable solution within Excel.

The Solution Using Power Query Interface

Power Query offers a powerful yet simple way to merge data without writing any code. Here’s a step-by-step guide to achieve this:

Load Data from Both Sheets:

1. Open your Excel workbook.
2. Go to the Data tab.
3. Select Get Data > From Other Sources > From Table/Range.
4. Load the data from Sheet1 and Sheet2 into the Power Query Editor.

Merge Queries:

1. With the Sheet1 query open in the Power Query Editor, go to the Home tab.
2. Click on Merge Queries > Merge Queries as New.
3. In the Merge window, select Sheet1 as the first table and Sheet2 as the second table.
4. Choose Study ID as the matching column for both tables.
5. Select Join Kind as Inner Join.
6. Click OK.

Expand the Merged Table:

1. After merging, you will see a new column with a table icon (usually named something like Sheet2).
2. Click the expand icon (two arrows pointing away from each other) in the column header.

2. In the expand options, uncheck Use original column name as prefix to avoid prefixing.
3. Select all columns to include them in the merged table except the duplicate Study ID.

Close & Load:

1. Click Close & Load to load the combined data into a new sheet.

Benefits of This Method

By using the Power Query interface, you can:

• Automate Data Merging: Easily refresh the data whenever new information is added, ensuring your analysis is always current.
• Enhance Accuracy: Reduce the risk of manual errors.
• Save Time: Quickly combine data with a few clicks, streamlining your workflow.

Conclusion

Whether you prefer the technical route of using M code or the user-friendly interface of Power Query, Excel provides powerful tools to enhance your data analysis capabilities. This project demonstrated the flexibility and efficiency of Power Query in merging and analyzing data.

I hope this series of articles provides valuable insights into using Power Query for data analysis. Feel free to reach out if you have any questions or need further assistance with your data projects.

For more insights and updates, follow my LinkedIn profile: Chen Wei Oh.