SQL View Issue - Recent update

Power BI
1

Hello All,

I havent used power bi for a while, but have created over 50 reports in the last few years.

I recently updated Power Bi Desktop, and am now getting some issues importing data which is driving me nuts!

I have a very simple SQL View that I am trying to use.
The view should return ~60 rows, but power bi is showing 600??

To try and diagnose the issue I manually entered the query (Note the SQL view excludes any dates before 2024-07-01

Efacs
Efacs687×533 13.7 KB

Once loaded going into edit query shows the following.

Result
Result730×718 33.8 KB

Has somehitng recently changed, or is there a setting I am missing somewhere?

Thanks in advance,

Mark

2

Hi @mark.roberts

Thank you for posting your query.

could you please run the query directly in your DB and cross validate the count once

select count(*) from emea_pbi_cutover
where duedate >=‘2024-07-01’

If you see the result count as 60 appxoximate then you can pls share the pbix file to investigate further ?

you can sanitize your confidential data before sharing

3

Hi Dhrubojit, Thanks for the reply.

Running
select count(*) from emea_pbi_cutover
where duedate >=‘2024-07-01’
Returns 60 rows.

As you can see from the view, I am already filtering the dates.

USE [efacdb]
GO
/****** Object:  View [dbo].[emea_pbi_cutover]    Script Date: 03/14/2024 12:28:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[emea_pbi_cutover] AS 
SELECT i.orderid,
i.itemnumber,
s.traderid, c.name,
i.partid, i.description,
i.traderquantity, 
dateadd(dd, datediff(dd,0,i.duedate),0) AS Due,
CASE 
  WHEN partid IN (SELECT PartID FROM newcoparts)
  THEN 'NewCo'
  ELSE 'APG'
END AS ManufacturingEntity,
i.status
FROM salesorderitems i
INNER JOIN salesorders s ON
s.id = i.orderid
AND i.duedate >= '2024-07-01'
AND i.status IN ('NEW','ACTIVE')
AND partid NOT LIKE 'CARRIAGE%'
INNER JOIN customers c ON 
c.id = s.traderid

When I import into Power Bi, I am getting 608 rows?

Adding a filter to the Due column does nothing?

Changing the data type from DateTime to Date, then adding a filer, works as expected?

The server is a legacy SQL Server 2005 due to be decomissioned in July - Could this, in conjunction with the recent update be the issue?

I have never had issues importing data before the update.

Kind Regards,

Mark
SQL Issue.pbix (51.6 KB)

4

Hi @mark.roberts,

Once upon a time (not just once :-)) I was in similar situation. Back then the situation was that the date format was changed ( MM and DD turn places).

It is big difference is it 1st July or 7th January.

So maybe for date to convert to date forcing YYYYMMDD format to avoid this kind of situation.

Good luck.

5

I’m not sure, but it seems to be some regional setting. Test it to see if it brings 60 lines

From: yyyy-mm-dd

SELECT *
FROM emea_pbi_cutover
WHERE duedate >= '2024-07-01'

To: yyyy-dd-mm

SELECT *
FROM emea_pbi_cutover
WHERE duedate >= '2024-01-07'
6

Thank’s for the replies everyone.

Just to clarify, the above view returns the predicted results, what I don’t understand is why/how is Power Bi somehow modifying the view?

7

@mark.roberts - SQL Server update modify the date format.

I had similar situation - same SQL script, same dataset - different result.

What you can do - to be sure use convert function in your SQL - something like this:

Convert(varchar, ‘20240107’,112) – 112 - for yyyymmdd format

Good luck.