charles sterling on 04 Aug 2018 03:20:21
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.
Administrator on 19 Jan 2019 03:22:09
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/
- Comments (11)
RE: Dataflows from Custom SQL Query
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.
let
Source = Sql.Database("SQ-ENT12-P02\PROD2", "RPT_CARE", [Query="Select [ProgramStatusId], [ProgramStatusCode], [ProgramStatusDescription], [ActiveStatus], [CertifiedStatus], [VerificationStatus] FROM [dbo].[Code_ProgramStatus]"])
in
Source
RE: Dataflows from Custom SQL Query
Dataflows can be a game changer - but Native Queries are a must for it.
RE: Dataflows from Custom SQL Query
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.
RE: Dataflows from Custom SQL Query
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.
RE: Dataflows from Custom SQL Query
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
RE: Dataflows from Custom SQL Query
I'd like to be able to use SQL native queries in Dataflow too!
RE: Dataflows from Custom SQL Query
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.
RE: Dataflows from Custom SQL Query
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.
RE: Dataflows from Custom SQL Query
Same for us. We need Dataflows to work with SQL native queries to take advantage of it.
RE: Dataflows from Custom SQL Query
Yes Please!