Power Query M Code Snippet for Merging Two Tables

This document provides a Power Query M code snippet that demonstrates how to merge two tables based on two or more columns, returning only the matching records.

Overview

• Functionality: Merge two tables on specified columns.
• Return Type: A table containing only the matching records based on the specified key columns.
• Input Validation: Checks for null or invalid inputs to ensure robustness.

Code Snippet

let
    // Function to merge two tables
    MergeTables = (table1 as table, table2 as table, keyColumns as list) as table =>
    let
        // Validate inputs
        _ = if Table.IsEmpty(table1) or Table.IsEmpty(table2) or List.IsEmpty(keyColumns) then
            error "One of the input tables is empty or key columns list is empty.",
        
        // Merging the tables based on the specified key columns
        MergedTable = Table.NestedJoin(table1, keyColumns, table2, keyColumns, "Merged", JoinKind.Inner),
        
        // Expand the merged records to include additional columns from table2
        ExpandedTable = Table.ExpandTableColumn(MergedTable, "Merged", Table.ColumnNames(table2))
    in
        ExpandedTable,

    // Example Usage
    Source1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"],
        [ID = 3, Name = "Charlie"]
    }),
    
    Source2 = Table.FromRecords({
        [ID = 2, Age = 30],
        [ID = 3, Age = 25],
        [ID = 4, Age = 40]
    }),
    
    // Call merge function with the source tables and key column
    Result = MergeTables(Source1, Source2, {"ID"})
in
    Result

Key Components

Function Definition

• MergeTables: This function merges two tables based on the columns specified in keyColumns.
  • Parameters:
    • table1: The first table to merge.
    • table2: The second table to merge.
    • keyColumns: A list of column names on which to merge both tables.
  • Return Type: A merged table containing only the matching records.
  • Exceptions Handled: Throws an error if any input table is empty or keyColumns is not provided.

Example Usage

• Source1 and Source2: Defines two example tables with "ID" and other relevant fields.
• Result: Calls the MergeTables function using these two sample tables with "ID" as the key for merging.

Conclusion

This Power Query M code snippet efficiently merges two tables based on multiple key columns while ensuring that only matching records are returned. By handling potential input issues and adhering to best practices, the provided code ensures scalability and maintainability for real-world data processing tasks.

For further learning and advanced techniques, consider exploring resources available on the Enterprise DNA Platform.