Direct Query to SQL View: Time only column not abl...

KateThePhoenix · ‎12-07-2020

I have a SQL Database that I'm working with, and person in charge of that has made a view for me, due to my need to have the date/time column split into Date and Time columns.

However, I can not create an hour bin for the time column (which PBI does recognizes as a Time column) which is what I need (right now it goes down to the second, not very helpful). It gives me this error:

So then I'm thinking maybe I can get away from just filtering things in the last 2 hours.

Again, I got an error, this time it looks like this:

So I reach out to the admin working on the database, thinking maybe the variable type is an integer, and that's why it's having issues, and he sends me this, saying he's confused as that value is definitely formated as Time:

Has anyone run into this before? Is this a formatting thing on my end or his end?

Additiotl Info: I do HAVE to be on a direct connect, as there are MILLIONS of rows of data in the database. I also can't share the report or database, as it has medical information in it that is sensitive.

Anonymous · ‎12-07-2020

In the query editor you can check the data type Power BI thinks it is. Click on the Transform tab then highlight your hour column. It should list the data type in the toolbar. If it's wrong, you can change it to Time and set the formatting you want. I had a similar thing happen with an experiment.

KateThePhoenix · ‎12-07-2020

Yeah, that was the first thing I checked, it does recognize the column as time.

Anonymous · ‎12-07-2020

I just whipped up a test page with some imported split date and time columns. The relative time slicer sounds like it should give similar behavior to your filter (they're advertised in the same place). I placed the time column into one and input 8 hours. The slicer looks like this:

Looks identical to your filter range output. Since my data is a few months old, I just punched in 10,000 hours to see if there's more going on here though.

You can see in this case it's actually subtracting time from the current date, not filtering by hourly bins. This is confirmed by the fact that my plot is displaying no data since there's no combined date/time column. I think this is corroborated by your second error message, since the filter could really be date/time, and it makes sense that filters and slicers use the same logic.

To be sure, I made a quick direct query mod in another report to grab date and time as separate columns, and the behavior is exactly the same with hour ranges before and after last midnight (although I'm not getting an error, just a blank plot in both cases).

It's hard to tell if it's actually filtering based on date/time behind the scenes, not time, when the range is less than a day, but the behavior seems to indicate it. That's frustrating. Might be a bug.

Also, found this from 3 years ago.