Connecting Two SQL Databases in M (Power Query)

Overview

This document outlines a function in M (Power Query) to connect and merge data from two SQL databases. This function will include necessary imports, comprehensive docstrings, input validation, inline comments, and a practical example for real-world usage.

Function Definition

Code

let
    // Function to connect to two SQL databases and merge their data based on a common key
    ConnectAndMergeSQLDatabases = (dbConfig1 as record, dbConfig2 as record, mergeKey as text) as table =>
    let
        // Validate input parameters
        ValidateInput = 
            if not Record.HasFields(dbConfig1, {"Server", "Database", "Username", "Password"}) then
                error "Invalid database configuration for the first database."
            else if not Record.HasFields(dbConfig2, {"Server", "Database", "Username", "Password"}) then
                error "Invalid database configuration for the second database."
            else if Text.IsNull(mergeKey) then
                error "Merge key cannot be null or empty.",
        
        // Establishing connections to the first database
        Source1 = Sql.Database(dbConfig1[Server], dbConfig1[Database], [CreateNavigationProperties = false, Username = dbConfig1[Username], Password = dbConfig1[Password]]),
        
        // Establishing connections to the second database
        Source2 = Sql.Database(dbConfig2[Server], dbConfig2[Database], [CreateNavigationProperties = false, Username = dbConfig2[Username], Password = dbConfig2[Password]]),
        
        // Getting the required tables from both databases
        Table1 = Source1[TableName1],  // Replace TableName1 with the actual table name
        Table2 = Source2[TableName2],  // Replace TableName2 with the actual table name
        
        // Merging the two tables based on the specified merge key
        MergedTables = Table.NestedJoin(Table1, {mergeKey}, Table2, {mergeKey}, "MergedData", JoinKind.Inner),
        
        // Expanding the merged table to get the required columns from the second table
        ExpandedMergedTable = Table.ExpandTableColumn(MergedTables, "MergedData", Table.ColumnNames(Table2))
    in
        ExpandedMergedTable
in
    ConnectAndMergeSQLDatabases

Documentation

• Function Name: ConnectAndMergeSQLDatabases
• Purpose: Connects to two SQL databases, retrieves specific tables, and merges them based on a common key.
• Parameters:
  • dbConfig1 (record): Configuration containing details for the first database (Server, Database, Username, Password).
  • dbConfig2 (record): Configuration containing details for the second database (Server, Database, Username, Password).
  • mergeKey (text): The column name to be used as the key for merging the data from both tables.
• Return Type: Returns a table containing merged rows based on the specified key.
• Exceptions: Errors will be raised for invalid database configurations or null/empty merge keys.

Input Validation

The function checks that:

• The database configurations include required fields (Server, Database, Username, Password).
• The merge key is not null or empty.

Comments and Logic

• The code includes comments to guide on each step, providing clarity on the process of connecting to databases, retrieving tables, merging, and expanding the data.
• The use of Table.NestedJoin allows for easy merging of tables, while Table.ExpandTableColumn ensures access to the fields from the second table.

Practical Example

Usage

To use this function, call it with the database configurations and the merge key:

let
    dbConfig1 = [Server = "ServerOne", Database = "DatabaseOne", Username = "User1", Password = "Password1"],
    dbConfig2 = [Server = "ServerTwo", Database = "DatabaseTwo", Username = "User2", Password = "Password2"],
    mergeKey = "CommonColumnName",  // Specify the column to merge on
    Result = ConnectAndMergeSQLDatabases(dbConfig1, dbConfig2, mergeKey)
in
    Result

Note

In the real-world example, replace "CommonColumnName" with the actual name of the column used to join the two tables and ensure proper error handling to manage connectivity issues effectively.

Conclusion

The provided M function allows seamless connections and data merging between two SQL databases, following best practices for maintainability and scalability. For further learning on Power Query and M language, consider exploring resources on the Enterprise DNA Platform.