Pasting complex SQL query into Power BI

[Anonymous]

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?

[torchesamuel]

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

[EVEAdmin]

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]

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.

[EVEAdmin]

---

@Anonymous wrote:

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.

---

Can you schedule automatic refresh?

[Anonymous]

Scheduling automatic refresh still doesn't solve the problem of running the server-side query. It's also not compatible with DirectQuery mode, if I understand Power BI correctly.

[parry2k]

@Anonymous how you are connecting to your Vertica data source

[parry2k]

@Anonymous got it, you are using native connector, why not you create a view in your vertica database and use that to feed to power bi

[Anonymous]

Yeah, I'm exploring adding this query as a view in the database, but since I'm not the database admin/owner, I'm dependent on others for that. Hopefully it'll pan out, but it might not.

Is there really no way to write a SQL query within Power BI? This seems like a rather fundamental request.

[parry2k]

@Anonymous you can give your own query to pull data

[Anonymous]

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