Sql.Databases

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Sql.Databases

What is Sql.Databases?

The Sql.Databases function is a M function that returns a table with metadata about the databases available on a SQL Server instance. It takes two arguments: the server name and an optional connection string.

Here is an example of how to use the Sql.Databases function:

let

server = "localhost",

databaseList = Sql.Databases(server)

in

databaseList

This code will return a table with metadata about the databases available on the “localhost” SQL Server instance.

Breaking Down the M Code

Let’s take a closer look at the M code behind the Sql.Databases function. Here is the M code:

(SqlServerDataSource(server, [HierarchicalNavigation=true])

{[Name="Databases"]}

)[Data]

This code is a bit complex, but we can break it down into three parts:

Part 1: SqlServerDataSource

The SqlServerDataSource function is a M function that establishes a connection to a SQL Server. It takes two arguments: the server name and an optional connection string.

Here is an example of how to use the SqlServerDataSource function:

let

server = "localhost",

database = "AdventureWorks",

connectionString = "Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=true",

sqlServer = SqlServerDataSource(connectionString)

in

sqlServer

This code will establish a connection to the “AdventureWorks” database on the “localhost” SQL Server instance.

Part 2: HierarchicalNavigation

The HierarchicalNavigation property is a boolean value that determines whether the SqlServerDataSource function should return a hierarchical view of the server’s databases and their objects.

Here is an example of how to use the HierarchicalNavigation property:

let

server = "localhost",

sqlServer = SqlServerDataSource(server, [HierarchicalNavigation=true])

in

sqlServer

This code will establish a connection to the “localhost” SQL Server instance and return a hierarchical view of its databases and their objects.

Part 3: {[Name=”Databases”]}[Data]

The final part of the code is a reference to a table within the hierarchical view returned by the SqlServerDataSource function. In this case, we are referencing the “Databases” table.

Here is an example of how to reference a table within a hierarchical view:

let

server = "localhost",

sqlServer = SqlServerDataSource(server, [HierarchicalNavigation=true]),

databasesTable = {[Name="Databases"]}[Data]

in

databasesTable

This code will establish a connection to the “localhost” SQL Server instance, return a hierarchical view of its databases and their objects, and then reference the “Databases” table.

The M code behind the Sql.Databases function might seem complex at first, but it is easy to understand once you break it down. By understanding how the M language works, you can unlock the full potential of Power Query and manipulate data in ways you never thought possible.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)