Oracle database
Power BI is a business analytics tool that allows users to visualize and analyze data from a variety of sources. One such source is an Oracle database. In order to connect to an Oracle database from inside Power BI, we need to use Power Query M Language. This language is used to transform and clean data, as well as establish connections to data sources.
What is Power Query M Language?
Power Query M Language is a functional programming language used to transform and clean data in Power BI. It is used to create queries that connect to data sources, transform data, and load the data into the Power BI data model. The language is similar to other programming languages, such as Python and R, but is designed specifically for data transformation.
Connecting to the Oracle database Data Source
In order to connect to an Oracle database from inside Power BI, we need to use the Power Query Editor. To open the Power Query Editor, click on the Edit Queries button on the Home tab of the Power BI ribbon.
From the Power Query Editor, we can create a new query by clicking on the New Source button on the Home tab of the ribbon. In the New Source dialog box, we can select the Oracle database as our data source.
In the Oracle database connection dialog box, we need to enter the connection details for the Oracle database, including the server name, database name, and credentials. Once we have entered the connection details, we can click on the OK button to connect to the database.
Writing Power Query M Language Code
After we have connected to the Oracle database, we can write Power Query M Language code to transform and clean the data. The code is written in the Advanced Editor, which can be accessed by clicking on the Advanced Editor button on the View tab of the Power Query Editor.
The Power Query M Language code is written in a scripting language that uses functions to transform and clean data. The language is designed to be easy to read and write, even for users who are not experienced programmers.
Here is an example of Power Query M Language code that connects to an Oracle database and loads data into Power BI:
let
Source = Oracle.Database(“server”, “database”, [Query=”SELECT FROM table”]),
#”Filtered Rows” = Table.SelectRows(Source, each ([column] = “value”)),
#”Sorted Rows” = Table.Sort(#”Filtered Rows”, {{ “column”, Order.Descending}})
in
#”Sorted Rows”
In this example, the code connects to the Oracle database and selects all rows from the table. It then filters the rows based on a specific column value and sorts the rows in descending order based on another column.
Conclusion
In conclusion, Power Query M Language is an essential tool for connecting to and transforming data from a variety of sources, including Oracle databases. By using Power Query M Language code, we can easily connect to an Oracle database from inside Power BI and load data into the data model. The language is designed to be easy to read and write, even for users who are not experienced programmers, making it a valuable tool for data analysts and business intelligence professionals.
