Using October 2017 Power BI Desktop (but I suspect the issue has always been present). I am using a en-GB locale, but while this affects the results, it is unlikely to be causal.
In the Query Editor, open a SharePoint Online document library using SharePoint Files connector (API v15).
There are several datetime fields which are identifiable as date-formatted by the following observation:
1) They have the date & time icon in the top left of the column header.
2) When you attempt to do a 'Change Type', they already show as a Date/Time type.
3) Clicking the field's dropdown menu shows that the filtering is selective according to a Date/Time typed data source.
However, the fields are actually not set as date/time fields. They are still text fields. This can be demonstrated by ordering the field from newest to oldest using the following line of code.
Table.Sort(Source,{{"Date modified", Order.Descending}})We now see that the field is being ordered as if it is a text field, not a date/time field. Dates such as 31 August 2017 are being incorrectly ordered as more recent than 05 November 2017 because the string "31/10/2017 00:00:00" > "05/11/2017 00:00:00"
The UK locale above accentuates this specific example, but even if we were using an en-US locale we would see the same issue with other dates. For example, 31 October 2016 and 05 October 2017 have problems in en-US because "10/31/2016 00:00:00" > "10/05/2017 00:00:00" as text, but not as a date field.
The workaround is to add a prior step that manually changes the field's data type before performing the field ordering. When we do that, we see that the field values become italic and right-justified - which supports the idea that only then, are they correctly formatted.
Please can you address this in as a matter of importance.
Many thanks,
Geoff Richardson
on:
URGENT!! Paginated Report not loading on Mozilla F...
mattlee
on:
“Add Value” in Textbox Not Visible in Dark Theme
on:
Bi Crashes on launch with (AS Process PID=XXXXXX H...
on:
Fix the Date function in DAX