I would like to be able to create an entity based off of a custom SQL Query instead of having to use the Power Query tools. The problem I am having is that one of the tables I am working with is too large and results in a timeout when Dataflows tries to bring it in. I think that a custom SQL Query will alleviate this issue by allowing me to prefilter my data before it comes into Dataflows.
13 Comments
- We shipped Native SQL query support. You can learn more about this feature here: https://powerbi.microsoft.com/en-us/blog/power-bi-dataflows-january-updates/
- If we can't use SQL native queries, Dataflow is useless for us...
- Yes Please!
- Same for us. We need Dataflows to work with SQL native queries to take advantage of it.
- This is possible if you first create the query in Power BI Desktop and reuse the M code in the blank query connection in Dataflows. However, this process should be alot easier and the SQL database connector today is too limited.
- Hi Kristoffer. You're correct you can create the SQL native queries however as soon as you try to achieve a schedule refresh you receive an error message saying native queries aren't supported.
- This is having a big impact on my reporting. For instance: when selecting a list of active tickets generated within the last 12 months, my database contains tickets generated since 2001. Instead of extracting 450 rows using my Oracle servers as the first layer, I have to extract a list of all tables, then more than a million rows of tickets and then apply M filtering (and a series of filters at that) to achieve the results I want. And this is oversimplifying the issues. Sometimes my queries will contain joins, subqueries, CASE statements and aggregates that can be processed server-side for ease of implementation, only sending the data I really need for PowerBI.
- I'd like to be able to use SQL native queries in Dataflow too!
- This is something that we have found ourselves by surprise in a client (since we have not seen in any detailed site this limitation). The solution we have found at the moment is to create a view with that query, and once created select that view from the list of objects that appear (tables and views) when connecting to the corresponding database. But, obviously, this is a workaround. We also need to execute native query in dataflows
- Dataflows can be a game changer - but Native Queries are a must for it.
