date format in pbi

[MP_123]

hi

im connecting direct query to ADW, in the database, i have a date field that for some reason, pbi convert it to date time. (for all dates show date + 12:00AM)

because im in direct, i cant transform the column but i can't tranform when connecting (sql query), im converting the colum and still, pbi add the time

is someone have an idea for me?

thanks a lot

[MattAllington · Accepted solution]

The column has not changed data types,. It is just a format. You can change the format on measures and on individual visualisations. Sorry I don't have my direct query instance in front of me to be more specific. 

[jirineoral]

@MP_123

Matt is right. It is just format. In field list on right side pick date column that displays as datetime and, on ribon go to modeling tab and pick correct format in format drop down

Jiri

[MattAllington]

I doubt PBI is converting it to date/time. More likely the column is formatted as date/time (In the same way I can format an integer as a decimal, like 1.00 and 2.00).  

In Power BI, just change the number format to Date, and all should be fine. 

[MP_123]

@MattAllington@ankitpatira

i know that i can transform a column.. but it disabled in direct query. "The step result in a query that is not supported on a DirectQuery mode" .

and i really checked it a few times, in the DB the data shown in date, and pbi adds the 12:00 AM to all of the data...

thanks again.. hope you'll have an idea

[ankitpatira]

@MP_123 as a test, instead of using pbi desktop you can connect straight to adw from power bi service and check that way if it gives same behaviour.

[MP_123]

thank you both!!

i understood what the format is... my mistake

and anyway, i think the DirectQuery capabilities is due to the data source too

thanks again

[ankitpatira · Accepted solution]

@MP_123 Here is the snapshot of options I get in query editor using DirectQuery mode. Are you sure you don't get these options ?

[MattAllington · Accepted solution]

The column has not changed data types,. It is just a format. You can change the format on measures and on individual visualisations. Sorry I don't have my direct query instance in front of me to be more specific. 

[MP_123]

@MattAllington thank you

actually i don't know what do you mean by format, and it's not a measure but date.

i just want to show the date in slicer without the time.

[ankitpatira]

@MP_123 Can you not click on Edit Queries, go to Query Editor, under Transform tab, change Data Type to Date.