Skip to main content

Power BI

Completed

Dataflows from Custom SQL Query

Vote (76) Share
charles sterling's profile image

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)
charles sterling's profile image Profile Picture

Power BI User on 05 Jul 2020 23:57:29

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

charles sterling's profile image Profile Picture

Josh Judy on 05 Jul 2020 23:37:44

RE: Dataflows from Custom SQL Query

Dataflows can be a game changer - but Native Queries are a must for it.

charles sterling's profile image Profile Picture

Frederick on 05 Jul 2020 23:37:36

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.

charles sterling's profile image Profile Picture

RCC on 05 Jul 2020 23:36:57

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.

charles sterling's profile image Profile Picture

Carlos Sacristan on 05 Jul 2020 23:36:47

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

charles sterling's profile image Profile Picture

Jim Daily on 05 Jul 2020 23:36:29

RE: Dataflows from Custom SQL Query

I'd like to be able to use SQL native queries in Dataflow too!

charles sterling's profile image Profile Picture

Kristoffer Absalonsen on 05 Jul 2020 23:35:57

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.

charles sterling's profile image Profile Picture

Jack Strudley on 05 Jul 2020 23:35:57

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.

charles sterling's profile image Profile Picture

Jack Strudley on 05 Jul 2020 23:35:56

RE: Dataflows from Custom SQL Query

Same for us. We need Dataflows to work with SQL native queries to take advantage of it.

charles sterling's profile image Profile Picture

Matt Winter on 05 Jul 2020 23:35:03

RE: Dataflows from Custom SQL Query

Yes Please!