As stated in the subject I'm having an issue with a SQL column formated as a Date type being recognized by the Query editor as Date/Time.
I have already disabled both "Auto date/time" and "Automatically detect column types and headers for unstructured sources" under the current file date load options.
I've attached an example of my SQL Statement below...
Cast(Timestamp as DATE) as JustDate,
Cast(Timestamp as TIME) as JustTime
Any ideas on how to prevent this from happening?
I know I can just convert the data type using M after loading the source but I would like to avoid the redundancy of repeating a data type conversion
Solved! Go to Solution.
Is there a solution to this? I am using DirectQuery to pull SQL data. Get the message that I cannot change the data type once the data has been applied because it is DirectQuery, so used Convert (date,dateField) in the query which results in a Date only field in SSMS when checking the results. But in PowerBI insists on adding the 00:00 time element. And I cannot seem to just format the table column to remove the time so looks really untidy.
@Tgilchrist: I would actually import those fields directly from SQL as DateTime, and then format the fields to show either or date or time. You will have to do this anyway to get the right format to show when you build the report.
This does cause a problem when you want to use the date field with slicers or as values in tables - having the redundant time element is just messy and looks as though you can't be bothered to format properly. Where in reality I can't find a way to format to just date.
I had a similar problem this week.
May I ask what is your data source?
I have not been able to test it yet, but hope to solve it using the "Date Only" transformation.
It's not causing any problems with the report as far as performance or modeling goes, it just seems like a redundant thing to do and I don't have much experience in PowerBi so I was hoping it may be something simple.
just testing a few combinations, It seems like nvarchar is always recognize as Text regardless if the content is in a date format and date is recognized as Date/Time
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps