Accessing Two SQL Databases in M (Power Query)

This document provides a structured approach to creating a Power Query function in M that accesses two SQL databases. The code connects to each database, retrieves data, and merges it into a single table.

Function Overview

Purpose

The function retrieves tables from two different SQL databases and merges them based on a specified join key.

Parameters

• db1ConnectionString: Connection string for the first SQL database (Text).
• db2ConnectionString: Connection string for the second SQL database (Text).
• table1Query: SQL query string or table name for data from the first database (Text).
• table2Query: SQL query string or table name for data from the second database (Text).
• joinKey: The key on which to merge the two datasets (Text).
• joinType: Type of join to perform; options: "Inner", "Outer", "Left", "Right" (Text).

Returns

• Returns a table resulting from merging the two datasets based on the specified join key.

Exceptions

• Throws an error if any connection string is invalid or if the specified tables/queries do not exist.

Code Implementation

// Import necessary modules
let
    // Define function to access and merge two SQL databases
    MergeSQLDatabases = (db1ConnectionString as text, db2ConnectionString as text, 
                         table1Query as text, table2Query as text, 
                         joinKey as text, joinType as text) as table =>
    let
        // Documentation
        /*
        This function merges data from two SQL databases based on a specified join key.
        
        Parameters:
        db1ConnectionString (Text): Connection string for the first database.
        db2ConnectionString (Text): Connection string for the second database.
        table1Query (Text): SQL query or table name from the first database.
        table2Query (Text): SQL query or table name from the second database.
        joinKey (Text): Column name on which to join the two tables.
        joinType (Text): Type of join to perform (Inner, Outer, Left, Right).
        
        Returns:
        table: Merged table result.
        
        Exceptions:
        Error: If connection or table/query is invalid.
        */

        // Connect to the first database and retrieve data
        Source1 = Sql.Database(db1ConnectionString, table1Query),
        // Connect to the second database and retrieve data
        Source2 = Sql.Database(db2ConnectionString, table2Query),

        // Perform join based on specified type
        MergedTable =
            if joinType = "Inner" then 
                Table.Join(Source1, joinKey, Source2, joinKey, JoinKind.Inner)
            else if joinType = "Outer" then 
                Table.Join(Source1, joinKey, Source2, joinKey, JoinKind.FullOuter)
            else if joinType = "Left" then 
                Table.Join(Source1, joinKey, Source2, joinKey, JoinKind.LeftOuter)
            else if joinType = "Right" then 
                Table.Join(Source1, joinKey, Source2, joinKey, JoinKind.RightOuter)
            else 
                error "Invalid join type specified. Use 'Inner', 'Outer', 'Left', or 'Right'."
    in
        MergedTable
in
    MergeSQLDatabases

Code Usage Example

To use the MergeSQLDatabases function, you will need to have valid connection strings and table queries for your SQL databases. Below is an example of how to call the function:

let
    // Define connection strings and queries
    db1ConnectionString = "Server=myServer1;Database=myDB1;User Id=myUser;Password=myPassword;",
    db2ConnectionString = "Server=myServer2;Database=myDB2;User Id=myUser;Password=myPassword;",
    table1Query = "SELECT * FROM Sales",
    table2Query = "SELECT * FROM Customers",
    joinKey = "CustomerID",
    joinType = "Inner",

    // Call the function to merge the datasets
    ResultTable = MergeSQLDatabases(db1ConnectionString, db2ConnectionString, 
                                     table1Query, table2Query, 
                                     joinKey, joinType)
in
    ResultTable

Summary

• The provided function enables seamless access and merging of data from two SQL databases.
• It supports different types of joins, offering flexibility according to user requirements.
• Ensure valid connection parameters are passed to avoid runtime exceptions.

For further learning on Power Query and advanced M coding, consider exploring courses on the Enterprise DNA Platform to enhance your skills.