Data Sources
M Database Source
Loading Database Data
M database source uses Sql.Database for SQL queries.
Introduction to M Database Source
The M database source allows you to connect and interact with SQL databases using the Sql.Database function in Power Query M. This function enables you to execute SQL queries, retrieve data, and manipulate it within the Power Query environment.
Connecting to a SQL Database
To connect to a SQL database, you need to specify the server name and the database name. The Sql.Database function forms the foundation of this connection. Here's how you can establish a basic connection:
In this example, replace ServerName and DatabaseName with your actual server and database names. This will create a connection object that you can use to execute queries.
Executing SQL Queries
Once connected, you can execute SQL queries against your database. Use the optional Query parameter in the Sql.Database function to specify your SQL statement.
This example demonstrates how to retrieve all records from the Customers table. By specifying the Query parameter, you direct the Sql.Database function to execute the provided SQL query.
Handling Query Parameters
Power Query M allows you to parameterize your SQL queries to make them dynamic. You can define parameters in M and pass them to your SQL query.
In this example, the CustomerID is parameterized, allowing you to dynamically filter the Customers table based on different customer IDs.
Advanced Query Options
The Sql.Database function offers several advanced options to optimize your queries and manage connections, such as setting command timeouts and encrypting connections. Here's a brief overview:
- CommandTimeout: Specifies the duration (in seconds) before a command times out.
- Encrypt: Determines whether the connection to the database is encrypted.
Data Sources
- Source Data
- Excel Source
- CSV Source
- Database Source
- Web Source
- JSON Source
- XML Source
- Folder Source
- Previous
- CSV Source
- Next
- Web Source
