Showcasing My Skills: Using Power Query M Code to Merge Sheets in Excel

In a recent project, I assisted a doctor in Canada who needed to merge two sheets within an Excel workbook for data analysis. This project allowed me to showcase my technical skills, particularly using Power Query in Excel to automate and streamline the data merging process.

The Challenge

The doctor had two separate sheets of data that needed to be analyzed together. Manually merging these sheets was not only tedious but also susceptible to errors. She needed an efficient, reusable solution that could handle this task seamlessly.

The Technical Solution

To address this challenge, I utilized Power Query, an advanced data connection technology that enables users to discover, connect, combine, and refine data across a wide variety of sources. Here’s how I implemented the solution:

1. Loading Data into Power Query: First, I loaded the data from both sheets into Power Query.
2. Merging the Sheets: Using an inner join based on a common column ("Study ID"), I merged the two sheets. This ensured that only the rows with matching "Study ID" values from both sheets were combined.
3. Using M Code for Automation:

let
    // Load the first sheet
    Source1 = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
    
    // Load the second sheet
    Source2 = Excel.CurrentWorkbook(){[Name="Sheet2"]}[Content],
    
    // Perform an inner join on the "Study ID" column
    MergedTables = Table.NestedJoin(Source1, {"Study ID"}, Source2, {"Study ID"}, "Sheet2Data", JoinKind.Inner),
    
    // Expand the merged table to include columns from both sheets except the duplicate "Study ID" column
    CombinedData = Table.ExpandTableColumn(MergedTables, "Sheet2Data", List.RemoveItems(Table.ColumnNames(Source2), {"Study ID"}))
in
    CombinedData
        

The Benefits

By using this method, the doctor could easily refresh the data whenever new information was added, ensuring that the analysis was always up-to-date and accurate. The process was efficient, error-free, and reusable for any future data sets.

Conclusion

This project not only provided a practical solution for the doctor but also highlighted the power and flexibility of Power Query in Excel. It’s a testament to how leveraging advanced Excel features can significantly enhance data analysis and workflow efficiency.

Stay tuned for the next article, where I'll recap this story and provide a user-friendly guide for those who prefer using the Power Query interface instead of writing M code.

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