Forum Discussion
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
- parry2kSuper User
Anonymous you can give your own query to pull data
- AnonymousNot 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.
- EVEAdminPost Patron
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.
- AnonymousNot 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.
- parry2kSuper User
Anonymous how you are connecting to your Vertica data source
- torchesamuelFrequent Visitor
Please was this solved apart from saving results in a table or views?
thank you