Direct Custom SQL Query Denodo from Power BI M Query

If you are planning to use Power BI on top of Denodo sometimes a base view or a Power Query won’t be enough. If you need to embed your own complex query to pull data from Denodo, there are a few items you must be aware of. See below on how to Direct Custom SQL Query Denodo from Power BI M Query.

1. Create a Data Connection to Denodo

2. Connect Power BI to Denodo using the ODBC Connections

3. Select Transform and Create a blank query in Power BI

• In the Advanced Editor, Place in your query which you can copy from below as a starter template
  1. All table names are case sensitive. The source queries were not and the developers may have missed some
  2. All table and column names need 2 double quotes on each side
  3. Basic SQL functions like Left or ConCat may give you issues. Be sure to read the documentation on other ways to emulate this functions.

Sample Query

let
    Source = Odbc.Query(“dsn=Denodo_ServerName”, “SELECT Sum(“”Amount””) AS Amount
            ,””Column1″”
            ,”” Column2″”
      FROM “”vw_Viewname””
      WHERE “” Column3″” = 0
            AND “” Column4″” = 0
      GROUP BY “” Column1 “”
            ,”” Column2 “””) 
in
    Source

Direct Custom SQL Query Denodo from Power BI M Query