Unable to sort date in chronological order

[Aksgr]

Hi all ,

I have dates (entire calender ) as columns hence i would have 365 collums. But they look jumbelled and I want it in chronological order.

I have attached the Screenshot please do take a look and help

[Aksgr]

Hi @selimovd  Thankyou for your responce,

Unfortunetly its not yet resolved. The issue is that I have column names of each date . Do look at these attached pics. 

[Anonymous · Accepted solution]

Hi @Aksgr ,

According to your screenshot, I know that your dates are in column headers. As far as I know, in this situation, Power BI will sort those column headers like text data. So 25/02/2023 will be considered smaller than 30/01/2023. 

Here I suggest you to unpivot those date columns and do some transormation to get a date column in date format.

Whole code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmMgNgVicyC2VIrViVZyArKMgNgEiM2A2AKk0kApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"01/01/2023" = _t, #"30/01/2023" = _t, #"01/02/2023" = _t, #"15/02/2023" = _t, #"28/02/2023" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"01/01/2023", Int64.Type}, {"30/01/2023", Int64.Type}, {"01/02/2023", Int64.Type}, {"15/02/2023", Int64.Type}, {"28/02/2023", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", Int64.Type}, {"Attribute.3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Day"}, {"Attribute.2", "Month"}, {"Attribute.3", "Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each #date([Year],[Month],[Day])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}})
in
    #"Changed Type2"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hey @Aksgr ,

if the column is from the type Date or DateTime, then it should automatically sort correctly.

Otherwise for the month name you have to set a sort by column:

https://learn.microsoft.com/power-bi/create-reports/desktop-sort-by-column

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic