EQuIS

Power BI is a powerful business intelligence tool that allows data analysts to create interactive reports and visualizations. One of the key features of Power BI is the ability to connect to various data sources, including the EQuIS data source. EQuIS is a database management system that is widely used in the environmental industry to store and manage environmental data.

EQuIS

In this article, we will explore how to use Power Query M Language code to connect to the EQuIS data source from inside Power BI.

Understanding Power Query M Language

Power Query is a data transformation and cleansing tool that is built into Power BI. It allows users to connect to various data sources, transform the data, and load it into Power BI. Power Query uses a functional language called M to perform data transformations.

M is a case-sensitive language that is similar to Excel formulas. It has a rich set of functions and operators that can be used to manipulate data. M code can be written in the Power Query Editor window in Power BI or in another text editor and then copied into Power BI.

Connecting to the EQuIS Data Source

To connect to the EQuIS data source from inside Power BI, we need to use the ODBC connector. The ODBC connector allows us to connect to any ODBC-compliant data source, including EQuIS.

EQuIS

Here are the steps to connect to the EQuIS data source:

1. Open Power BI and click on “Get Data” on the Home tab.

2. In the “Get Data” window, select “ODBC” and click “Connect”.

3. In the “ODBC” window, select the EQuIS ODBC driver from the list of drivers. If the driver is not listed, you may need to install it on your computer.

4. Enter the connection details for the EQuIS database, including the server name, database name, and login credentials. You may also need to enter additional settings depending on your EQuIS configuration.

5. Click “Connect” to establish the connection to the EQuIS database.

Once the connection is established, we can use Power Query M Language code to transform the data.

Using Power Query M Language Code

Power Query M Language code can be used to perform various transformations on the data, including filtering, grouping, and aggregating. Here is an example of M code that retrieves data from the EQuIS data source and filters it based on a specific date range:


let

Source = Odbc.DataSource(“dsn=EQuISmssql”, [HierarchicalNavigation=true]),

dbo_v_sample = Source{[Schema=”dbo”,Item=”v_sample”]}[Data],

#”Filtered Rows” = Table.SelectRows(dbo_v_sample, each [sample_date] >= #date(2022, 1, 1) and [sample_date] <= #date(2022, 1, 31))

in

#”Filtered Rows”


This code retrieves data from the “v_sample” view in the EQuIS database and filters it based on the “sample_date” column. The resulting data is returned as a table.

Power Query M Language code can also be used to create calculated columns and measures. Here is an example of M code that calculates the average value of a column:


let

Source = Odbc.DataSource(“dsn=EQuISmssql”, [HierarchicalNavigation=true]),

dbo_v_sample = Source{[Schema=”dbo”,Item=”v_sample”]}[Data],

#”Added Custom” = Table.AddColumn(dbo_v_sample, “Average Value”, each [value] / [count]),

#”Removed Other Columns” = Table.SelectColumns(#”Added Custom”,{“sample_date”, “location_name”, “Average Value”})

in

#”Removed Other Columns”


This code adds a calculated column called “Average Value” to the “v_sample” table in the EQuIS database. The calculated column divides the “value” column by the “count” column to calculate the average value. The resulting table includes only the “sample_date”, “location_name”, and “Average Value” columns.

Conclusion

Power Query M Language code is a powerful tool that allows data analysts to connect to various data sources and transform the data. By using M code, we can perform complex data transformations that are not possible with the built-in Power Query transformations.

In this article, we explored how to use Power Query M Language code to connect to the EQuIS data source from inside Power BI. We also looked at some examples of M code that retrieve, filter, and calculate data from the EQuIS database. With this knowledge, you can begin using Power Query M Language code to transform your own data and create powerful visualizations in Power BI.

Power BI Training Courses by G Com Solutions (0800 998 9248)

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)

    Similar Posts