Forum Discussion

Frbelotto's avatar
Frbelotto
Frequent Visitor
2 years ago
Solved

Power Bi vs Power Query group by

Help! I own a huge database containing millions of rows of transactions. It does contain many columns with diverse information :     I am working on a power bi dashboard resuming such inform...
Need Help
  • RossEdwards's avatar
    RossEdwards
    2 years ago

    Two methods to achieve this.  In the Source row of your query, click the cog icon and paste your SQL code into the box under "Advanced".

     

    I prefer to use a function called Value.NativeQuery().  Its used like this (advanced editor):

     

    let
    Source = <Your current source row>,
    QueryResult = Value.NativeQuery(Source, "
<SQL GOES HERE>
    ")
in
    QueryResult

     

RossEdwards's avatar
RossEdwards
Solution Sage
2 years ago

The Power Query editor preview window only uses a portion of the source data in its display.  When doing a 'close and apply', the full database dataset will be configured.  This can create instances where you get different numbers.

 

There are also occasions where unordered data can be considered in chunks in Power Query which can cause odd behaviour.  My recommendation is to group your data using an SQL query (or similar if on a different database).  Get the database to do the 'Group By' for you as part of the query statement.  It will likely perform faster too.

  • Frbelotto's avatar
    Frbelotto
    Frequent Visitor
    2 years ago

    Thanks for the information. I tried using a similar SQL query on the db and I got same results of the preview. 

     

    Anyway, how could I set the SQL query instead of power query? I was trying to see the "created query" to try understand what was wrong but I couldn't find how.