Power Bi vs Power Query group by

[Frbelotto]

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 information, so, to reduce my input base for, I was using the "group by" on the power query, so, I am grouping such table by (command created by point and click options of power query) :

{"STATUS", "PRODUTO", "PARCEIRO", "MARCA", "data_transação"}, {{"Agg_Valor_Transação", each List.Sum([Valor_Transação]), type nullable number}, {"Agg_Receita", each List.Sum([RECEITA]), type nullable number}, {"Agg_Cashback", each List.Sum([CASHBACK]), type nullable number}})

It does work in power query as you can see below

But as long as a close power query, the same table in power by shows me this values!

And wors! Every time I edit the query and open it in power by the results are increased!

obs : Such table currently does not contain any relationship defined in the model

[RossEdwards · Accepted solution]

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

[Frbelotto]

Thanks!
A quick question. My data source table name is a kind weird.
A must use the table name under "", and the inside "" is breaking the sql text code. On python I solve this by using ' instead of ", but I couldnt do this on the PWBI advanced editor.

[RossEdwards]

The " character will be considered a special character, all you need to do is use it twice.  Chanage your code to:

FROM DB2I023A. ""ShoppingBB"" t1

[RossEdwards]

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]

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.