Forum Discussion
Date sorting
Just for fun I downloaded the facebook data for a page I manage. I downloaded both the excel and the csv file.
The date in the files is US format, but I'm Australian and the datetime format of PowerBI uses the local machine settings which in my case is UK format.
The excel document is badly formatted. It can't be opened in PowerBI. I open it in Excel then save as xslt. Excel reformats the dates to local (UK) format and fixes the file format. PowerBI easily imports this new file and date and graphs it correctly.
When I open the csv document in PowerBI I am unable to set the posted column to date/time because PowerBI can't process the US date formats when the local settings are UK.
I suggest taking the date column, splitting it based on the / character then building a new date column by recombining these values. This new column will be in the correct date format and sort correctly.
For reference: https://community.powerbi.com/t5/Desktop/How-to-change-the-date-format/td-p/40460
It may also be that you have the column set to date in the report model but not in the data model. The visualisation might be using the data model rather than the report model settings. I'd try this first.
Hi Ormesome,
Thanks for your help!
The main date column in my post data is formatted as 'mm dd yyyy' (this is how it is in the excel) and my power bi locale settings are US.
Perhaps the problem is that I've formatted this date manually into a new column 'Date AU' and used this on the graphs? (Date AU = format('POST DATA'[Date],"dd mmm hh:mm")). When I go to the query editor, this column isn't there as it's not from the original data set.
Is there another way around this? If I pull in the 'date' column onto my graph, it brings up the date heirachy, but this doesn't include 'time', which i need for the graph.
Hopefully this all makes sense, thank you for your help!
Sonia