Create custom query in Desktop

[mdubose]

I have a need to replace Business Objects reports with Power BI.  In Business Objects, you're able to create derived tables which allows you to enter SQL expresssions.  Is there a way to do the same in Power BI Desktop?  I'm connecting to a mySQL database via ODBC connection. Thanks in advance.

EDIT:

My SQL expressions are complex. They include subqueries. For example:

select a.id, namefirst, nameLast from (

(select id, clobValue as nameFirst from table A where name = 'nameFirst') a,

(select id, clobValue as nameLast from table A where name = 'nameLast') b

)

where a.id = b.id

[Padycosmos]

This video might help

https://www.youtube.com/watch?v=cTxCiOXoCps&list=PLApPcvU5-R24K3mbxORV7T3ckVLfDjmHF&index=15

[mdubose]

Hi @Padycosmos ,

In this video, it demonstrates added a SQL query when connecting to a SQL data source. I'm connecting via ODBC to a MySQL data source so I do not have the option to enter the query this way.

[lbendlin]

You can run multiple queries against the same data source (and they can be custom SQL). Each query will appear as a table in Power BI if you choose to load it.

[mdubose]

Hi @lbendlin ,

Where in Desktop, can I create this query?

[lbendlin]

You do that in the ETL portion called Power Query Editor.

[mdubose]

Hi @lbendlin,

Using this feature in Desktop is new to me, can you provide a screenshot of where in Power Query Editor, I could enter the query?  Thank you.

[Padycosmos · Accepted solution]

Expand Advanced to get the SQL statement window