Sorting Hours and Minutes

[AJ007]

I have a test dataset where I have one date column and two columns with two different time values (hour:minutes) for the same date.

If any of those two time columns is selected in a slicer, it gives me a weird date in the slicer. This happens when the column data type is "Time".

If I change the column data type to "Text" , I can get the actual time values in the slicer but they are sorted without keeping AM and PM values in mind.

For example, time values 7:30 AM, 8:20 AM, 10 AM, and 2 PM will sort like this - 10:00 AM, 2:00 PM, 7:30 AM, 8:20 AM

I want them to sort in the correct AM and PM order.

Please help.

[v-yulgu-msft · Accepted solution]

Hi @AJ007,

---

If any of those two time columns is selected in a slicer, it gives me a weird date in the slicer. This happens when the column data type is "Time".

---

What does "a weird date in the slicer" look like? Please show us the screenshot. In my test, the date and time column are both displayed correctly.

---

For example, time values 7:30 AM, 8:20 AM, 10 AM, and 2 PM will sort like this - 10:00 AM, 2:00 PM, 7:30 AM, 8:20 AM

I want them to sort in the correct AM and PM order.

---

To reset the sort ordering, please refer to this thread. To create the auxilliary table which is used to sort, please create a blank query in Query Editor mode and paste below code into "Advanced editor".

let
    Source = List.Times(#time(0, 0,0), 1440, #duration(0, 0, 1, 0))
in
    Source

Convert the query to table.

Change the column type to "Time", add an index column.

Best regards,

Yuliana Gu