Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Pasting complex SQL query into Power BI

I have been using Toad Data Point to query a large Vertica database with a query given to me by a peer. The query is a rather frightful mixture of joins and window functions that mere mortals like me daren't mess with. I would like to simply copy/paste this query into PowerBI Desktop in Direct Query mode. However, I haven't found a way to do this. I can access the underlying tables in Power BI, but any attempts to create this (essentially) view over the underlying tables has stumped me. 

My query starts with this:

SELECT
mytablename.*,
CASE WHEN tbl1.column1 IS NULL THEN tbl2.col3 ELSE tbl1.col4 END AS mynewcolumnname,
CASE WHEN tbl2.column2 IS NULL THEN tbl2.col3 ELSE tbl1.col5 END AS mysecondcolumnname
, ROW_NUMBER(
  ) OVER (  PARTITION  by 
  
                  sometable.somecolumn, sometable.anothercolumn
                order by
                    sometable.examplecolumn,
                    sometable.examplecolumn2,
                    sometable.examplecolumn3 desc) yetanothername
FROM
(SELECT
  B.baz,

et cetera


This query goes on for 200+ lines. I have no authority to change the underlying schema; I'm stuck with this.

Is there a feature that would allow me to simply paste this SQL query into Power BI desktop and have it show up as a table (or view or whatever) from which I can create visualizations?

10 Replies

  • Anonymous you can give your own query to pull data

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Parry, that query textbox might be there for a SQL Server data source, but I don't see it with a Vertica data source.

  • When it comes to very complex queries, I prefer to run them individually in SQL Server and insert the results into a table. Then I get POwerBI to query that table.

    • Anonymous's avatar
      Anonymous
      Not applicable

      EVE, If I did that, I would have to run the server-side query each time I wanted to use the dashboard, wouldn't I?

       

      That's not very user-friendly, is it? Telling my users, "hey, before you go to powerbi.com for your reports, first log on to this other system and run this query," is a non-starter.

      • parry2k's avatar
        parry2k
        Super User

        Anonymous how you are connecting to your Vertica data source

  • torchesamuel's avatar
    torchesamuel
    Frequent Visitor

    Please was this solved apart from saving results in a table or views? 
    thank you