Powerquery seemingly merging lines by itself when using SQL connection

[Anonymous]

Hello all, I've run into a weird issue and I have no clue if it is a bug or some other config I messed up.

I have my PBI connected to a DB using ODBC, everything looks fine, however the past 7 days I've noticed some of my numbers were off and then I started searching for the culprit. I've realized that for some reason PBI is merging some lines into a single one, seemingly at random and the numbers do not add up so that is causing the issue I had observed.

To make things clearer here is an exerpt of my sales table inside my DB 

Highlighted, on 2021-02-19 I have 7 sales of the same item from different stores (the columns that matter from left to right are salesdate; store_id; internalproductid; salesquantity; grosssale;netsales;grosscost) being 2 sales made from the same store ( 04 0048).

However after importing the data to PBI Here is what I end up with.

Just 4 lines for some reason  two of the sales (54.99) merged into one sale of (109.98) and the quantity (qtdven) merged as well being shown as 2 units sold. Aside from that I have 3 other sales merging into a huge (141.98) sale, however, the quantity this time stayed as a single item. 

I am not well versed into SQL but I believe the SQL statement ( used in powerquery to import the data) has nothing out of the ordinary to cause this behavior

SELECT
R.salesdate AS data,
R.internalproductid AS CODPRO,
R.salesquantity AS QTDVEN,
R.grosscost AS CUSTO,
R.returnquantity AS DEVOLUCAO_CUSTO,
sum(R.netsales - R.netsalereturn) AS VENDA_LIQ,
sum(R.grosssale - R.grosssalereturn) AS VENDA_BRUT
FROM resultado_vendas_pbi R
where 1=1
AND R.segment IN ('1000','9000','10000')
GROUP BY --R.CODLOJA,TO_DATE(R.MES,'YYYY-MM-DD'),B1_PROC,R.CODPRO
R.salesdate,
R.internalproductid,
R.salesquantity,
R.grosscost,
R.returnquantity
ORDER BY
R.salesdate

And it happens to other items as well, the sales always add up however the quantity sold does not (as we could see from the example above).

Does any one have a clue on why/ what is happening ?

[Anonymous]

Indeed the SQL statment used in powerquery was grouping some values and causing the issue.

[edhans · Accepted solution]

You may want to ask this in a SQL forum since you are using a SQL statement directly. I recommend against using direct SQL statements in that advanced window. Instead I recommend:

1. Do your transformations in Power Query. It will fold many of the SQL statements back to the server so the server does the work anyway.
2. Create a view in SQL and connect to that. Power Query treats tables and views the same, so you can easily continue to fold statements after the final code in the View.

Power Query isn't doing any summarizing of your data unless you are asking it to. Missing records might be suspect, but records added together, correctly from a math standpoint, would be something your code is asking it to do.