Same exact SQL query returns 0 rows in Power BI

[jdubs]

This is a new one. Very simple SQL query returns 0 rows in Power BI. I have literally copied and pasted the query directly from SQL. And no, I am not drunk. 

[jdubs · Accepted solution]

Very strange. I used ODBC as the data source and it worked fine. Maybe it isn't returning all rows? The particular values (i.e. "True") aren't even showing as available values when I filter the column. There are only about 9 rows (out of hundreds of thousands) that meet this criteria. 

Anyway, moving on. 

[jdubs]

This is the x64 build of Power BI Desktop btw

[jdubs]

So this is in Power Query. I'm trying to set up the data source. I select SQL Server, enter the server name and database and then enter my query as above. I copied and pasted it directly from my SQL server where I know the syntax is correct and returns data. However, no data is returned here.

Oddly, if I select ODBC as my data source and enter all the same information, data is returned correctly.

Of note is that I just updated to the Dec. '23 release of Power BI and am wondering if that has something to do with it. I have never had this problem before. 

[Fowmy]

@jdubs 

I just connected to a local host SQL server from Power BI Dec 2023 version and it works:

Check if any permission issues exist.

[jdubs]

Select column1, column2, column3, column4 from table where trueorfalsevalue = 1

It loads the columns but 0 rows are returned

[Fowmy]

@jdubs 

Not enough context and details to understand your issue. could share more info, wher have you written the code? show the result of the T-SQL result and in the Power BI.

[Fowmy]

@jdubs 

Can you show how you have written the DAX query based on the T-SQL ?