Appending Data from Two SQL Databases in M (Power Query)

This document outlines a Power Query function to connect to two SQL databases, extract data from specified tables, and append the results into a single output table. Follow the structure below for best practices in code organization, validation, and usage documentation.

Function Overview

Function: AppendDataFromSqlDatabases

let
    AppendDataFromSqlDatabases = (ConnectionString1 as text, TableName1 as text, ConnectionString2 as text, TableName2 as text) as table =>
    let
        // Validate the input parameters
        ValidateParameters = () => 
        if (Text.Length(ConnectionString1) = 0 or Text.Length(TableName1) = 0 or 
            Text.Length(ConnectionString2) = 0 or Text.Length(TableName2) = 0) then 
            error "All parameters must be non-empty text strings.",

        // Execute SQL query to fetch data from the first database
        Source1 = Sql.Database(ConnectionString1, TableName1),

        // Execute SQL query to fetch data from the second database
        Source2 = Sql.Database(ConnectionString2, TableName2),

        // Append the two tables
        AppendedData = Table.Combine({Source1, Source2})

    in
        AppendedData
in
    AppendDataFromSqlDatabases

Documentation

• Function Purpose: Connects to two SQL databases, retrieves data from specified tables, and appends the data into a single resulting table.
• Parameters:
  • ConnectionString1 (text): Connection string to the first SQL database.
  • TableName1 (text): Name of the table in the first SQL database to be queried.
  • ConnectionString2 (text): Connection string to the second SQL database.
  • TableName2 (text): Name of the table in the second SQL database to be queried.
• Returns: A single table containing the combined results from both tables.
• Exceptions: Raises an error if any strings are empty or if the connection fails.

Input Validation

Robust input validation is implemented to ensure user inputs are non-empty strings. If not, an error is raised.

Code Logic Explanation

1. Parameter Validation: Ensures that connection strings and table names are provided.
2. Data Retrieval: Uses Sql.Database to fetch data from both specified tables.
3. Data Appending: Combines the resulting tables into a single table using Table.Combine.

Example Usage

This example demonstrates how to call the AppendDataFromSqlDatabases function:

let
    // Define connection strings and table names
    connStr1 = "Server=server1;Database=database1;User Id=username;Password=password;",
    tableName1 = "TableA",
    connStr2 = "Server=server2;Database=database2;User Id=username;Password=password;",
    tableName2 = "TableB",

    // Call the function to append data from both databases
    ResultTable = AppendDataFromSqlDatabases(connStr1, tableName1, connStr2, tableName2)
in
    ResultTable

Summary

This function allows data scientists and analysts to efficiently append data from two SQL databases, streamlining data integration tasks. Users are encouraged to explore further capabilities on the Enterprise DNA Platform to enhance their data querying skills.