Odbc.DataSource
Any database an ODBC driver on this computer reaches: its databases, schemas, tables and views to navigate.
Group: Databases and lakes. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Odbc.DataSource(connectionString as any, optional options as nullable record) as table| Parameter | Type | Description |
|---|---|---|
connectionString | any | A data source name ("dsn=Warehouse"), a connection string, or a record of its attributes ([dsn = "Warehouse"]). |
options | nullable record optional | HierarchicalNavigation: true to go through the schemas, false (the default) for each database's tables in one list. |
Returns
table. The databases (Name, Data, Kind "Database"), then, with HierarchicalNavigation, their schemas (Kind "Schema"), then the tables and views, each row with Name, Data, Schema, Item and Kind.
Remarks
- A record's attributes are joined into a connection string, name=value;...: texts and numbers, braces around a value with a semicolon or brace in it.
- A driver without databases starts at its schemas or tables. The driver's own catalogs (information_schema, sys, pg_catalog), temporary tables and synonyms are left out; the rest are in order of name, case aside.
- Give no user or password in the connection string: the sign-in holds them. Credentials are never part of the M: the studio asks for a sign-in the first time this computer reads the source and keeps it on this computer only, for you.
- A table's Data is read through the driver when it is used; to have the database filter or group first, use
Odbc.Query.
Examples
Down to a table, schema by schema
let
Source = Odbc.DataSource("dsn=Warehouse", [HierarchicalNavigation = true]),
Sales = Source{[Name = "Sales"]}[Data],
Dbo = Sales{[Name = "dbo"]}[Data],
Orders = Dbo{[Name = "Orders"]}[Data]
in
OrdersResult: not shown here: it needs an ODBC data source on this computer.