{"id":891,"date":"2020-07-30T19:54:55","date_gmt":"2020-07-30T19:54:55","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=891"},"modified":"2020-07-31T17:55:35","modified_gmt":"2020-07-31T17:55:35","slug":"direct-quer-denodo-from-power-bi-m-query","status":"publish","type":"post","link":"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/07\/30\/direct-quer-denodo-from-power-bi-m-query\/","title":{"rendered":"Direct Custom SQL Query Denodo from Power BI M Query"},"content":{"rendered":"\n

If you are planning to use Power BI on top of Denodo <\/a>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.<\/p>\n\n\n\n

1. Create a Data Connection to Denodo <\/p>\n\n\n\n

2. Connect Power BI to Denodo using the ODBC Connections<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

3. Select Transform and Create a blank query in Power BI<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
  • 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<\/li>
    2. All table and column names need 2 double quotes on each side<\/li>
    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. <\/li><\/ol><\/li><\/ul>\n\n\n\n
      \"\"<\/figure>\n\n\n\n

      Sample Query<\/strong><\/p>\n\n\n\n

      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<\/p>\n\n\n\n

      Direct Custom SQL Query Denodo from Power BI M Query<\/p>\n","protected":false},"excerpt":{"rendered":"

      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 […]<\/p>\n","protected":false},"author":1,"featured_media":900,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,11],"tags":[100,84],"class_list":["post-891","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-data-warehouse","tag-denodo","tag-sql-server"],"_links":{"self":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/891","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=891"}],"version-history":[{"count":5,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/891\/revisions"}],"predecessor-version":[{"id":905,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/891\/revisions\/905"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/900"}],"wp:attachment":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=891"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=891"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=891"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}