Dataflows from Custom SQL Query
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.
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/
I am struggling with this too. And need a solution. I created my tables by creating the table as a datasource first. the query runs perfect and returns my data and creates a table in Power BI. I copied the 'M' code from the data source. and tried to create a dataflow using the 'Blank Query'. I pasted the 'M' code into the Source - see below - and selected the data gateway for the this source. No dice! The system comes back with an error: 'There is an error in the query' ARGH!!! so frustrating. and there is no place to go to get feedback.
Source = Sql.Database("SQ-ENT12-P02\PROD2", "RPT_CARE", [Query="Select [ProgramStatusId], [ProgramStatusCode], [ProgramStatusDescription], [ActiveStatus], [CertifiedStatus], [VerificationStatus] FROM [dbo].[Code_ProgramStatus]"])
Dataflows can be a game changer - but Native Queries are a must for it.
The data source "blank query" is not working while refreshing. This is a must have functionality (pre-filter data). I understand that dataflow is still in preview, so probably (and I hope) it will be available soon.
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.
Carlos Sacristan commented
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
Jim Daily commented
I'd like to be able to use SQL native queries in Dataflow too!
Jack Strudley commented
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.
Kristoffer Absalonsen commented
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.
Jack Strudley commented
Same for us. We need Dataflows to work with SQL native queries to take advantage of it.
If we can't use SQL native queries, Dataflow is useless for us...