Less records than in SQL view

[BPyL]

Hi everybody.

I'm new in PowerBI but a SQL advanced user.

I have created a SQL View that shows 5 records. When I import this view link into PowerBI, it shows me only 2 records. 

These are the PoweBI resulting records:

And these are the View result (View_Logistica_01), 5 records.

Does anybody can help me?

Thanks.

[BPyL · Accepted solution]

It is solved.

Searching the sql queries that uses these sql views, I noticed the problem has appeared just on friday.

Why? The only answer possible was in the queries that are getting different data only on friday.

One of they is checking if the next day from GETDATE() is saturday, if so, it gets data from next monday. I use the DATENAME function, here is a query portion:

(CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 1, GETDATE())) = 'Saturday' 
THEN 2 ELSE 0 END)

Well, when I run this query on friday in my sql server, it returns 'Saturday' but when PowerBI runs it, it returns the spanish name 'Sábado'. So, the records on next monday are not showed in PowerBI because the CASE statement is not true.

Finally I have changed the query above to this other:

(CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 1, GETDATE())) IN ('Saturday', 'Sábado')
THEN 2 ELSE 0 END)

And it works.

Have a nice day.

[rohit_singh]

Hi @BPyL ,

I assume you have added the same queries to power query as in your screenshot. What I think might be happening is that power query is loading only the final query, which is view_logistica_01b, which is why you're seeing only two results. 
What you can do is append your SQL query with ; select * from view_logistica_01 so that this is the last statement. Maybe that gives you all the rows since the main table will be evaluated last.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!  🙂

[BPyL]

Hi Rohit and thank you for your answer.

The only query I use in powerBI is the first one, view_logistica_01 with his 5 records. The other 2 queries are depending views, only if you want to see.

Finally, I think it seems to be a spanish date format issue, because the day number 13 is interpreted as month number 13. I will adjust the CONVERT function in the query and I will answer here again for a better community knowledge.

Kind regards