How can we improve Power BI?

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.

79 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Admincharles sterling (Admin, Microsoft Power BI) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    10 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Josh commented  ·   ·  Flag as inappropriate

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

      • Frederick commented  ·   ·  Flag as inappropriate

        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.

      • RCC commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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

      • Jack Strudley commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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.

      Feedback and Knowledge Base

      Ready to get started?

      Try new features of Power BI today by signing up and learn more about our powerful suite of apps.