How to access datamart query from Power BI desktop

[jhalverson]

Hi all,

I have created a query in a datamart on Power BI Service, but am not sure how to access it on Power BI Desktop. The first image is the list of tables and the sql query I wrote that combines info from said tables. The second image is what pulls up when I connect to the datamart (in the form of a sql server database) on power bi desktop. The list of available tables does not contain my query "ConnectViolationsReport". 

Is it possible to access my query from Power BI Desktop? Or is it possible to write the same SQL query somewhere in Power BI desktop? I know when you select SQL Server as a datasource it gives you an option to write the query there, but I am not sure what the name of the database would be as this is a datamart.

Any help is greatly appreciated, thanks!

[otravers · Accepted solution]

You can't see SQL queries from an external SQL server in Power BI Desktop, you can only see their result if the query results in a view/table. (You currently can't define SQL views in Datamarts.)

You can add a SQL statement to your Source step in Power BI Desktop provided you connect via the Sql.Database connector and not the PowerBI.Datamarts connector. Connect to your SQL endpoint via SSMS to find your database name as it's needed for the SQL connector to work.

See this entry for further details:

https://www.oliviertravers.com/power-bi-datamarts-sql-server-desktop-tools-connection/