Informix.Database

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Informix.Database

The Informix.Database connector enables you to connect to an Informix database server and retrieve data from it. The connector uses the Power Query M function, which is a functional programming language that is used to define data transformations and manipulations. In this article, we will explore the M code behind the Informix.Database connector and how it works.

Connecting to an Informix Database

To connect to an Informix database using Power Query, you need to have the following information:

– Database server name or IP address

– Database name

– Port number

– Authentication method (Windows or database authentication)

– Username and password (if using database authentication)

Once you have this information, you can use the following M code to connect to the database:

let

serverName = “serverName”,

dbName = “dbName”,

port = 1234,

authMethod = DatabaseCredential,

username = “username”,

password = “password”,

connectionString = “DRIVER={IBM INFORMIX ODBC DRIVER};SERVER=”+serverName+”;DATABASE=”+dbName+”;HOST=”+serverName+”;SERVICE=”+port+”;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.utf8;DB_LOCALE=en_US.utf8;”,

source = Odbc.DataSource(connectionString, [HierarchicalNavigation=true, Credential=authMethod, UID=username, PWD=password])

in

source

This code uses the Odbc.DataSource function to create a connection to the Informix database. The connectionString variable contains the connection string that is used to connect to the database server. The source variable contains the result of the Odbc.DataSource function, which is a table that contains the data from the database.

Querying Data from an Informix Database

Once you have established a connection to the Informix database, you can use the Power Query editor to query data from it. The editor provides a user-friendly interface that allows you to perform various data transformations and manipulations. Behind the scenes, the editor generates M code that is used to execute the transformations.

For example, if you want to retrieve all the records from a table called "Customers", you can use the following M code:

let

serverName = “serverName”,

dbName = “dbName”,

port = 1234,

authMethod = DatabaseCredential,

username = “username”,

password = “password”,

connectionString = “DRIVER={IBM INFORMIX ODBC DRIVER};SERVER=”+serverName+”;DATABASE=”+dbName+”;HOST=”+serverName+”;SERVICE=”+port+”;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.utf8;DB_LOCALE=en_US.utf8;”,

source = Odbc.DataSource(connectionString, [HierarchicalNavigation=true, Credential=authMethod, UID=username, PWD=password]),

dbo_Customers = source{[Schema=”dbo”,Item=”Customers”]}[Data]

in

dbo_Customers

This code creates a connection to the Informix database using the Odbc.DataSource function, and then retrieves the Customers table using the source{[Schema="dbo",Item="Customers"]}[Data] expression. The result is a table that contains all the records from the Customers table.

Manipulating Data from an Informix Database

Power Query provides a wide range of data manipulation and transformation functions that you can use to modify the data retrieved from an Informix database. These functions allow you to aggregate, filter, sort, and reshape your data in various ways.

For example, if you want to filter the records from the Customers table to only include customers from a specific city, you can use the following M code:

let

serverName = “serverName”,

dbName = “dbName”,

port = 1234,

authMethod = DatabaseCredential,

username = “username”,

password = “password”,

connectionString = “DRIVER={IBM INFORMIX ODBC DRIVER};SERVER=”+serverName+”;DATABASE=”+dbName+”;HOST=”+serverName+”;SERVICE=”+port+”;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.utf8;DB_LOCALE=en_US.utf8;”,

source = Odbc.DataSource(connectionString, [HierarchicalNavigation=true, Credential=authMethod, UID=username, PWD=password]),

dbo_Customers = source{[Schema=”dbo”,Item=”Customers”]}[Data],

filteredCustomers = Table.SelectRows(dbo_Customers, each [City] = “Seattle”)

in

filteredCustomers

This code retrieves the Customers table from the Informix database, and then filters the records to only include customers from Seattle using the Table.SelectRows function. The result is a table that contains all the records from the Customers table that have Seattle as their city.

The Informix.Database connector in Power Query allows you to connect to an Informix database server and retrieve data from it using the M function. The M code behind the connector can be used to connect to the database, query data, and manipulate it in various ways. With Power Query, you can transform your data into the format that you need for your analyses and reporting.

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)