Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Date sorting

Hi,    I've posted this quesiton previously, but still can't seem to solve the problem.    I have a list of Facebook posts, which need to be ordered by the post date/time, but it is still orderin...
Need Help
Tips and Tricks
Ormesome's avatar
Ormesome
Helper II
8 years ago

Have you tried going to the data model and setting the field to a date data type?

Alternatively, on the report under modelling you can also set the field to a date data type.

 

I find that date and locations tend to require explicit modelling for correct sorting and rendering.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    Hi Ormesome,

    Yes, it's formatted to be date/time, so I'm not sure why it's not sorting correctly ?

    Any other ideas?

    Thanks,
    Sonia

    • Ormesome's avatar
      Ormesome
      Helper II
      8 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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