No one, absolutely no one wants to work with dates in Power Query/DAX/Power BI. But we can't avoid the date/DateTime data type as most fact tables are recorded around time and/or date.

It was another day and another power query project (not a data analysis this time. It was a really fun project, We'll talk about this project later), I wanted to add a slicer onto a Pivot table. The user needs to choose a week show on the first date of the week and the last date of the week.

First day of the week - Last day of the week

5/2/2023 - 11/2/2023

I used the continues date column to get the first and last dates of the week, I did a little concatenation, and it returns the intended text values and all.

\= Text.From( Date.StartOfWeek([Date]))

& " - " &

Text.From( Date.EndOfWeek([Date]))

But,

When I sort the slicer it is not sorted properly. 😱

Why this happens...? 🤔

Here is the thing about this column, this is a text column and it is sorted as a text value. so nothing wrong with how it's sorted it sorts the value with a minimum number of characters first.

Let's take a look at it again, 1/1/2023, 1/51/2023.....

Return values make perfect sense...

So how do we sort these text values ad dates or at least as texts but in month number order?

We need to add a leading zero to dates and months so that when the text value is sorted it will work properly as the number of characters are same for all values.

How to add leading zero?🤔

Here is my formula for the new column

= Text.PadStart( Text.From (Date.Month(Date.StartOfWeek( [Date]))),2,"0") & "/"

& Text.PadStart( Text.From (Date.Day(Date.StartOfWeek( [Date]))),2,"0") & "/"

& Text.From (Date.Year(Date.StartOfWeek( [Date]))) & " - "

& Text.PadStart( Text.From (Date.Month(Date.EndOfWeek( [Date]))),2,"0") & "/"

& Text.PadStart( Text.From (Date.Day(Date.EndOfWeek( [Date]))),2,"0") & "/"

& Text.From (Date.Year(Date.EndOfWeek( [Date])))

and here is the sorted column

The key function here is the Text.PadStart( ).

You can use it to equalize a number of characters of values in the column and use a filler character (0 in this case) to fill the blank spaces from the beginning.

Conclusion ✍️

I managed to add this to a dropdown slicer in the Pivot table and got the intended results. The system I created for the user works fine so far (more on that later).