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.
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.
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.
