Denodo
Power BI is a powerful data visualization tool that allows users to connect to a variety of data sources. One such data source is Denodo, a data virtualization platform that allows users to access and integrate data from multiple sources. In this article, we will explore how to connect to a Denodo data source from inside Power BI using Power Query M Language code.
What is Power Query M Language?
Power Query is a data connection and transformation tool that is built into Power BI. Power Query M Language is the language used to create custom queries and transformations in Power Query. This language is similar to Excel formulas and is used to automate data cleansing and transformation tasks.
Connecting to Denodo Data Source from Inside Power BI
To connect to a Denodo data source from inside Power BI, follow these steps:
1. Open Power BI Desktop and select “Get Data” from the Home tab.
2. In the “Get Data” window, select “Blank Query” under the “Other” category.
3. In the Query Editor, select “Advanced Editor” from the “View” tab.
4. In the Advanced Editor window, enter the following M Language code:
let
Source = Odbc.DataSource(“dsn=DenodoDSN;UID=Username;PWD=Password;”, [HierarchicalNavigation=true]),
dbo_Table = Source{[Schema=””,Item=”dbo.Table”]}[Data]
in
dbo_Table
5. Replace “DenodoDSN” with the name of your Denodo ODBC data source name, “Username” with your Denodo username, and “Password” with your Denodo password.
6. Replace “dbo.Table” with the name of the Denodo view or table you want to connect to.
7. Click “Done” to save the query.
Explaining the M Language Code
Let’s break down the M Language code used to connect to the Denodo data source:
let
This keyword is used to declare the start of a new query.
Source = Odbc.DataSource(“dsn=DenodoDSN;UID=Username;PWD=Password;”, [HierarchicalNavigation=true]),
This line of code creates a new data source using the Denodo ODBC driver. Replace “DenodoDSN” with the name of your Denodo ODBC data source name, “Username” with your Denodo username, and “Password” with your Denodo password.
dbo_Table = Source{[Schema=””,Item=”dbo.Table”]}[Data]
This line of code retrieves the data from the Denodo view or table named “dbo.Table”. Replace “dbo.Table” with the name of the Denodo view or table you want to connect to.
in
dbo_Table
This line of code ends the query and returns the data from the Denodo view or table.
Conclusion
Connecting to a Denodo data source from inside Power BI is a simple process that can be accomplished using Power Query M Language code. By using this code, users can easily retrieve data from their Denodo data sources and create custom queries and transformations to visualize their data in Power BI.
