Forum Discussion
Sql commands not allowed in Direct query mode?
Is it only the SELECT sql command which is allowed in Direct query mode?
I get error message when I try to update my PowerBI Desktop project with a L language Direct query mode script that run a sql expression that start with the USE <db name> command. (There was no error message when saving the M script in the Advanced editor).
yes and no. You can run whatever SQL injection you want, little Bobby Tables. HOWEVER !!! Chris Webb's BI Blog: Why Is Power BI Running My SQL Query Twice? (crossjoin.co.uk) In short - don't get any ideas. It's not worth it.
2 Replies
I got a very excellent explanation and solution from Chris Webb in another post.
Direct query syntax returns data in PowerQuery perfectly but shows error when update PowerBI project
He noted that "Basically, if you can't use your SQL query as a subquery then you can't use it as a source in DirectQuery mode."
To my great relief all my trouble dissapeared when he explaned the workaround to apply table valued function as a source in DirectQuery mode TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery
yes and no. You can run whatever SQL injection you want, little Bobby Tables. HOWEVER !!! Chris Webb's BI Blog: Why Is Power BI Running My SQL Query Twice? (crossjoin.co.uk) In short - don't get any ideas. It's not worth it.