Forum Discussion

KateThePhoenix's avatar
KateThePhoenix
Regular Visitor
5 years ago

Direct Query to SQL View: Time only column not able to bin.

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
KateThePhoenix
Regular Visitor
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.