The output of the same query in SQL and in PowerBI is different

[Anonymous]

Hello,

I have an issue while uploading data from the SQL server database. The output of the same query in SQL and in PowerBI is different (PowerBI misses some values and marks them as 'null'). Here is an example of exactly the same 10 rows in SQL Studio and in PowerBI.

Will be really thankful for the help with fixing this issue!

[selimovd]

Hey @Anonymous ,

can you show us the query in SQL Server and in Power BI?

Did you try to use the exact same query in Power BI:

Do you still get different results in that case?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Hi @selimovd ,

Yes, I've tried the exact same query in Power BI as in SQL and I still get different results. It is just for this column that is represented on a photos that I've sent, everything else is the same. 

[selimovd]

Hey @Anonymous ,

can you post the query?

Do you use any special function? To be honest for me an SQL statement always gave the identical results so far.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

@selimovd , here is the simplification of a query (just deleted unrelevant here values and renamed tables). EIdentity is the variable that is unproperly represented in Power BI.
Yes, it is so strange cause till now SQL statement always gave the identical results. I've also tried to change the format of EIdentity to varchar(255) but it did not give any improvements.

DECLARE @START DATETIME = '01-01-2028'
DECLARE @END DATETIME = getdate();
 
with customers AS (
    SELECT 
    u.CustomerId
   ,da.Name
   ,u.AId

    from Table1 u
    left join Table2 da WITH (NOLOCK) on ...

	where ...
 
),

ext as (
select 
em.EIdentity

from
Table3 cv
left join Table4 em on ...
left join Table5 u on ...

where ...

)

SELECT
C.CustomerId
,ext.EIdentity

FROM Table6 b WITH (NOLOCK)
INNER JOIN customers C ON ...
left join ext on ...

WHERE ...

[selimovd · Accepted solution]

Hey @Anonymous ,

the query looks normal for me. But the behavior doesn't make sense.

In your case I would use the SQL Server Profiler and check what exactly the SQL Server is doing and if there are different queries executed for some reason.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[selimovd]

Hey @Anonymous ,

just because I'm curious, could you solve it with the SQL Server Profiler?

What was the reason for the different results?

Thanks and have a great day

Denis

[Anonymous]

Hi @selimovd ,

Yes, the thing was with the datatype of two values by which I've joined (one was datetime and one datetime2) so it caused small inconsistencies in values and some things were missed. So it is actually not the issue of Power BI.

Thanks and have a good day!

[selimovd]

Hey @Anonymous ,

I'm happy to hear that 🙂
Yes, it's usally small differences like that that make the difference.

Have a great day.

Best regards

Denis