Forum Discussion
Direct query syntax returns data in PowerQuery perfectly but shows error when update PowerBI project
I wrote a blog post about this:
Basically, if you can't use your SQL query as a subquery then you can't use it as a source in DirectQuery mode. The only workaround I'm aware of is this: https://prologika.com/power-bi-directquery-with-parameterized-stored-procedure/
You can use a table valued function as a source in DirectQuery mode though: https://blog.crossjoin.co.uk/2022/02/20/tsql-table-valued-functions-and-dynamic-m-parameters-in-power-bi-directquery/
I wrote a blog post about this:
Basically, if you can't use your SQL query as a subquery then you can't use it as a source in DirectQuery mode. The only workaround I'm aware of is this: https://prologika.com/power-bi-directquery-with-parameterized-stored-procedure/
You can use a table valued function as a source in DirectQuery mode though: https://blog.crossjoin.co.uk/2022/02/20/tsql-table-valued-functions-and-dynamic-m-parameters-in-power-bi-directquery/
Confirmed. The workaround works.